DAX Patterns

This section explores common and advanced patterns for writing DAX (Data Analysis Expressions) queries and calculations in SQL Server Analysis Services (SSAS) Tabular models and Power BI.

Common DAX Patterns

Time Intelligence

DAX provides powerful functions for performing time-based calculations, such as year-to-date, month-to-date, and year-over-year comparisons.

Year-to-Date (YTD) Sales

Calculate the cumulative sales from the beginning of the year to the current date.

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

Previous Year Sales

Compare current period sales with the same period in the previous year.

Previous Year Sales = CALCULATE(SUM(Sales[SalesAmount]), SAMEPERIODLASTYEAR('Date'[Date]))
        

Year-over-Year Growth

Calculate the percentage difference in sales compared to the previous year.

YoY Growth % = DIVIDE( [Sales] - [Previous Year Sales], [Previous Year Sales] )
        

Rankings

Determine the rank of items based on a specific measure.

Sales Rank by Product

Rank products based on their total sales amount.

Sales Rank = RANKX(ALLSELECTED(Products[ProductName]), [Sales], , DESC, Dense)
        

Averages and Ratios

Calculate various types of averages and performance ratios.

Average Sales per Transaction

Calculate the average value of each sales transaction.

Avg Sales per Transaction = AVERAGEX(Sales, Sales[SalesAmount])
        

Customer Retention Rate

Measure the percentage of customers who return over a period.

Retention Rate = DIVIDE(
    CALCULATE(DISTINCTCOUNT(Sales[CustomerID]), FILTER(Sales, Sales[Date] >= DATE(YEAR(TODAY()), 1, 1) && Sales[Date] < DATE(YEAR(TODAY())+1, 1, 1))),
    CALCULATE(DISTINCTCOUNT(Sales[CustomerID]), FILTER(Sales, Sales[Date] >= DATE(YEAR(TODAY())-1, 1, 1) && Sales[Date] < DATE(YEAR(TODAY()), 1, 1)))
)
        

Advanced DAX Patterns

Iterators (X-functions)

Learn how to use iterator functions to perform row-by-row calculations across a table or expression.

Calculating Profit Margin per Product

Iterate through each product to calculate its profit margin.

Product Profit Margin =
AVERAGEX(
    Products,
    DIVIDE(
        (SUM(Sales[SalesAmount]) - SUM(Sales[CostAmount])),
        SUM(Sales[SalesAmount])
    )
)
        

Context Transition

Understand how `CALCULATE` and filter context work together to modify the evaluation context.

Sales for Top N Products

Calculate sales for only the top N performing products.

Sales for Top 5 Products =
CALCULATE(
    [Sales],
    TOPN(
        5,
        ALLSELECTED(Products[ProductName]),
        [Sales],
        DESC
    )
)
        

Handling Many-to-Many Relationships

Strategies for managing and calculating measures in models with many-to-many relationships.

Virtual Relationships

Using DAX to simulate relationships where none physically exist in the data model.