Common DAX Scenarios

This page provides practical examples and solutions for common scenarios encountered when working with DAX (Data Analysis Expressions) in SQL Server Analysis Services.

1. Time Intelligence Calculations

Calculate year-to-date (YTD) sales, previous period comparisons, and moving averages using built-in DAX time intelligence functions.

Example: Year-to-Date Sales

This measure calculates the sum of sales from the beginning of the current year up to the current date.


YTD Sales =
TOTALYTD(
    SUM(Sales[SalesAmount]),
    'Date'[Date]
)

Key functions used:

TOTALYTD: Calculates the total of an expression evaluated over a period that is the beginning of the current year up to the last date specified in the expression.
SUM: Aggregates the sales amount.

DAX Time Intelligence Reference

2. Ranking and Top N Analysis

Determine the rank of products, customers, or regions based on sales or other metrics. Identify the top N items.

Example: Top 5 Products by Sales

This calculated column identifies the top 5 products based on their total sales.


Product Rank =
IF(
    RANKX(
        ALL('Product'),
        CALCULATE(SUM('Sales'[SalesAmount]))
    ) <= 5,
    "Top 5",
    "Others"
)

Key functions used:

RANKX: Ranks an expression evaluated over a table.
ALL: Returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied.
CALCULATE: Changes the context in which an expression is evaluated.

DAX Ranking Functions Reference

3. Ratio to Parent and Percentage of Total

Calculate a measure's value as a percentage of its parent category or the grand total.

Example: Percentage of Category Sales

This measure calculates the sales of a product as a percentage of the total sales for its category.


% of Category Sales =
DIVIDE(
    SUM(Sales[SalesAmount]),
    CALCULATE(
        SUM(Sales[SalesAmount]),
        ALLSELECTED('Product'[ProductName])
    )
)

Key functions used:

DIVIDE: Safely performs division.
ALLSELECTED: Returns all the rows in a table or all the values in a column, removing all context filters from the specified columns and rows, but the data context of the outer query remains.

DAX Context Transition Reference

4. Running Totals

Create a running total for a measure over a specific period, such as daily, monthly, or yearly.

Example: Monthly Running Sales Total

This measure calculates the cumulative sales for each month.


Monthly Running Sales =
CALCULATE(
    SUM(Sales[SalesAmount]),
    FILTER(
        ALLSELECTED('Date'[Month]),
        'Date'[Month] <= MAX('Date'[Month])
    )
)

Key functions used:

FILTER: Returns a table that has been filtered.
ALLSELECTED: Returns all the rows in a table or all the values in a column, removing all context filters from the specified columns and rows, but the data context of the outer query remains.
MAX: Returns the largest value in a column.

5. Dynamic Measures and Parameter Tables

Allow users to select which measure to display or analyze using parameter tables.

Example: Dynamic Sales Measure Selection

This scenario often involves creating a disconnected table for measure selection and using a `SWITCH` statement to dynamically change the displayed measure.


-- Sample Measure Table
MeasureSelection = DATATABLE(
    "Measure Name", STRING,
    "Measure Formula", STRING,
    {
        { "Total Sales", "SUM(Sales[SalesAmount])" },
        { "Average Sales", "AVERAGE(Sales[SalesAmount])" },
        { "Quantity Sold", "SUM(Sales[OrderQuantity])" }
    }
)

-- Dynamic Measure Definition
Selected Measure =
VAR SelectedMeasureName = SELECTEDVALUE(MeasureSelection[Measure Name])
RETURN
    SWITCH(
        SelectedMeasureName,
        "Total Sales", SUM(Sales[SalesAmount]),
        "Average Sales", AVERAGE(Sales[SalesAmount]),
        "Quantity Sold", SUM(Sales[OrderQuantity]),
        BLANK()
    )

Key functions used:

DATATABLE: Creates a table.
SELECTEDVALUE: Returns the value when the context for the column has been filtered down to one distinct value; otherwise, returns the alternate result.
SWITCH: Evaluates an expression against a list of values and returns the result corresponding to the first matching value.

Additional Resources

Explore the following resources for more in-depth DAX information: