Advanced DAX — variables, iterators, context transition — Power BI Module 7

Once you can write CALCULATE, the next jump is learning to compose: variables to organise complexity, iterators to compute per-row aggregates, and an understanding of context transition that prevents the most subtle DAX bugs. This module is the bridge from 'I can write DAX' to 'I can debug other people's DAX'.

Variables — VAR and RETURN

text

// Without variables — readable but recomputes SUM many times
Margin tier =
  IF(SUM(Sales[Profit]) / SUM(Sales[Revenue]) > 0.4, "High",
     IF(SUM(Sales[Profit]) / SUM(Sales[Revenue]) > 0.2, "Medium", "Low"))

// With variables — cleaner, faster, easier to read
Margin tier =
  VAR Profit = SUM(Sales[Profit])
  VAR Revenue = SUM(Sales[Revenue])
  VAR Margin = DIVIDE(Profit, Revenue)
  RETURN
    SWITCH(TRUE(),
      Margin > 0.4, "High",
      Margin > 0.2, "Medium",
      "Low"
    )

VAR captures a value once and reuses it. Faster, clearer, easier to debug — and modern DAX style mandates them in any non-trivial measure.

Iterators — SUMX, AVERAGEX, RANKX

Iterators take a table and evaluate an expression for each row, then aggregate. They're the only way to compute things like 'revenue minus cost per row' when revenue and cost live in different tables, or to rank items by a computed measure.

text

// Average order size, evaluated per order then averaged
Avg order = AVERAGEX(Sales, Sales[Quantity] * Sales[UnitPrice])

// Rank customers by lifetime sales
Customer Rank = RANKX(
  ALL(Customer[CustomerID]),
  [Lifetime Sales]
)

Both functions iterate, then aggregate. The first argument is the table; the second is the expression to evaluate per row.

Context transition

Context transition — the most misunderstood DAX concept

When you call CALCULATE (or any measure, since measures implicitly wrap their expression in CALCULATE) inside a row context, the row context is CONVERTED to filter context for that single row. The result: [Lifetime Sales] inside SUMX(Customer, [Lifetime Sales]) returns each customer's own lifetime sales (because the current customer row becomes a filter), then sums them. This is incredibly useful and incredibly confusing on first encounter.

Implication: a measure called inside an iterator works as if the iterator's current row were a slicer. This makes per-row calculations using existing measures trivial — but it's also the cause of mysterious performance problems when context transition runs over a million-row table.

Exercise

Write a DAX measure that finds the top customer by sales in the current filter context, and returns that customer's name. You'll need VAR, RANKX, FILTER, and probably CALCULATE. Use variables so the logic is readable. How does the measure respond when you slice by Region?