Iterators and Row Context
Per-row passes inside columnar aggregation
SUMX, AVERAGEX and friends iterate row-by-row: each row evaluated against a row context, then aggregated. Row context is the second half of DAX's engine.
What you will learn
- Explain row context and where it comes from
- Use iterators for per-row expressions
- Combine iterators with filter context correctly
What is this?
Iterators (SUMX, COUNTX, AVERAGEX, MAXX) evaluate an expression per row of a table, then aggregate the results. Row context is the mental 'current row' that makes those per-row expressions meaningful. When an iterator meets a filter context — via CALCULATE — you get row context plus filter context, the two-context model that powers every advanced DAX pattern.
Why do you need it?
- AOV, weighted prices, blended rates — all need per-row math before aggregation.
- Iterators express business rules that a plain SUM cannot.
- Understanding row context eliminates a whole class of 'blank/wrong' measures.
- Row context vs filter context confusion is the #1 DAX interview trap — mastering it pays off.
Mental model
A quality inspector checking every box on a conveyor
An iterator
The inspector (SUMX) applies a per-box rule (row expression) to each box in turn — rows passing the rule get tallied, then the line's total is reported. Filters decide which boxes reach the belt; the inspector decides how each box is scored.
How it works
- The iterator takes a table expression and a per-row expression.
- For each row it establishes row context — column references resolve to that row's values.
- The per-row results are then aggregated (summed, averaged, maxed).
- Filter context restricts the rows the iterator sees initially.
- CALCULATE inside the row expression triggers context transition — row context becomes filter context.
Iterator flow: filter, iterate, aggregate.
Examples
When you need the iterator
-- Per-row discount applied THEN summed (the correct order of operations):Net Revenue = SUMX(fact_orders, fact_orders[amount] * (1 - fact_orders[discount])) -- Plain SUM can't express the rule:-- SUM(fact_orders[amount]) * (1 - SUM(fact_orders[discount])) -- wrong! -- Average order value per row, then mean:AvgLineTotal = AVERAGEX(fact_orders, fact_orders[amount] * (1 - fact_orders[discount]))SUMX computes each row's net first, then sums — the only way to honor a per-row discount. AVERAGEX shows the same pattern for means.
In production
- Order-level revenue with per-line discounts and surcharges — impossible without iterators.
- A blended FX measure converts each transaction at its own rate, then aggregates.
- A margin report iterates over rows to apply column-level category rules before SUM.
Best practices
- Iterate on the narrowest table that carries the per-row rule.
- Use iterators when the expression genuinely depends on per-row values.
- Combine with CALCULATE deliberately — each use triggers context transition cost.
- Add filters before iterating so the row pass runs on fewer rows.
Common mistakes
- Using SUM on a compounded value instead of iterating rows.
- Expecting row-context column references to see the current filter implicitly.
- Heavy iterators over wide raw tables killed the performance.
- Missing CALCULATE, so an inner aggregation ignores outer filter context.
Debugging tips
- Blank rows from an iterator → confirm the row expression never errors (DIVIDE, type casts).
- Slowness → verify the iterator isn't scanning a huge load or losing a filter.
- Totals not matching → check whether filters apply before the row pass.
Interview checkpoint
Hands-on lab
- Reproduce the SUMX per-row pass in SQL
- Distinguish derived-then-sum from sum-then-derive
- Verify against a hand-computed control total
Emulate an Iterator in SQL
45 min · advanced
Quick quiz
SUMX(fact_orders, fact_orders[amount] * fact_orders[rate]) does what?
Row context and filter context are the same thing.