DAX Variables, Iterators, Filters and Windows

What it is

DAX measures are formulas that a semantic model evaluates in the filter context of each visual cell. The DP-600 outline names five groups of DAX skills: variables, iterators, table filtering, windowing and information functions.

The exam shows a requirement and asks you to choose, complete or fix a measure. Knowing what each function does to the filter context is more important than memorizing long formulas.

How it works

Variables

VAR stores a value or a table; RETURN gives the result. Variables make a measure easier to read, and each one is evaluated only once.

Margin % =
VAR SalesAmt = [Total Sales]
VAR CostAmt = [Total Cost]
RETURN
    DIVIDE ( SalesAmt - CostAmt, SalesAmt )

A variable is evaluated where it is defined, not where it is used. CALCULATE ( SalesAmt, REMOVEFILTERS ( 'Product' ) ) still returns the filtered value, because SalesAmt was already computed.

Iterators

An iterator walks a table row by row in a row context, evaluates an expression for each row, then aggregates.

Sales Amount = SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )

Product Rank =
RANKX ( ALLSELECTED ( 'Product'[Product Name] ), [Total Sales], , DESC, Dense )

When an iterator calls a measure, such as SUMX ( Customer, [Total Sales] ), each row's context is turned into a filter. This context transition is what lets the measure return each customer's own sales.

Table filtering with CALCULATE

% of All Categories =
DIVIDE (
    [Total Sales],
    CALCULATE ( [Total Sales], REMOVEFILTERS ( 'Product'[Category] ) )
)

Large Order Sales =
CALCULATE (
    [Total Sales],
    FILTER ( Sales, Sales[Quantity] * Sales[Net Price] > 1000 )
)

A simple column condition is enough for Red. A condition that multiplies two columns needs FILTER over the table.

Windowing functions

WINDOW, OFFSET and INDEX return rows relative to the current one, ordered by ORDERBY and optionally grouped by PARTITIONBY.

Previous Month Sales =
CALCULATE (
    [Total Sales],
    OFFSET ( -1, ALLSELECTED ( 'Date'[Year Month] ), ORDERBY ( 'Date'[Year Month], ASC ) )
)

Running Total =
CALCULATE (
    [Total Sales],
    WINDOW (
        1, ABS, 0, REL,
        ALLSELECTED ( 'Date'[Year Month] ),
        ORDERBY ( 'Date'[Year Month], ASC )
    )
)

Information functions

Information functions test the context or a value instead of calculating.

Sales or Average =
IF (
    ISINSCOPE ( 'Product'[Product Name] ),
    [Total Sales],
    AVERAGEX ( VALUES ( 'Product'[Product Name] ), [Total Sales] )
)

ISINSCOPE is the right test for hierarchy levels in a matrix. SELECTEDVALUE ( col, alternate ) is the short way to read a single selected value.

A worked example

A matrix shows categories and products. The business wants each product's sales, its rank within its category, last month's sales, and the category average on category rows.

1. Total Sales uses SUMX over quantity times price.
2. Product Rank uses RANKX ( ALLSELECTED ( 'Product'[Product Name] ), [Total Sales] ); under a category row, the category filter still applies, so ranks restart per category.
3. Previous Month Sales uses OFFSET ( -1, ... ) on the Year Month column.
4. Sales or Average uses ISINSCOPE to switch between a value and an average.

How the exam asks about this

• "Calculate revenue from quantity and unit price on each row." SUMX ( Sales, Sales[Quantity] * Sales[Unit Price] ).
• "Show the share of each category in the total of all categories." DIVIDE by CALCULATE with REMOVEFILTERS on Category.
• "Return last month's sales next to this month's." OFFSET ( -1, ... ) with ORDERBY on the month column.