Iterators, row context and context transition

The idea

Most DAX you have written so far reads a whole column: SUM(order_items[line_total]). An iterator is the other way to read a table. SUMX, AVERAGEX, MAXX, RANKX and COUNTX take a table and an expression, visit the table one row at a time, work the expression out on that row, and only then collapse the results (add them, average them, rank them).

While the iterator stands on a row, the columns of that row are available by name. That "current row" is the row context. It is a position in a table, nothing more. It is not a filter, and that difference is behind most iterator bugs.

Why it exists

Harbor & Pine's Finance Controller wants net revenue built from quantity, unit_price and discount_pct. The number she needs does not exist as a column: it is a product, per line, that then has to be added. SUM can only add a column that is already there.

A spreadsheet user would add a helper column, fill it down, and sum it. An iterator is that helper column without the column: the multiplication happens in memory, for exactly the rows the cell is allowed to see, and nothing is stored in the model. That keeps the file smaller and the number alive: change the slicer and the rows being walked change with it.

The X functions are a family with one shape: SUMX, AVERAGEX, MINX, MAXX and COUNTX differ only in how they collapse the per-row results. Learn one and you have learned all of them.

How it works under the hood

SUMX(table, expression) runs in two stages, and the order matters.

1. Decide which rows exist. The filter context of the cell (row header, slicers, CALCULATE filters) shrinks table first. A SUMX over order_items in the Lighting row of the matrix walks only Lighting lines.
2. Walk them. For each remaining row, evaluate the expression with that row as the row context, then combine the results.

Because the expression runs once per row, it can use columns of the row (order_items[quantity]) and, through a many-to-one relationship, columns of the row's parent (RELATED(products[unit_cost])). It cannot reach down the other way without help: from a product you do not automatically see its lines.

Context transition

That "without help" is the interesting case. Inside a calculated column or an iterator, a plain SUM(order_items[line_total]) ignores the row you are on. The current product is a position, not a filter, so nothing reaches order_items.

CALCULATE fixes this. When it runs inside a row context it takes every column of the current row and turns them into filters, then evaluates its expression under those filters. This is context transition. For product 135 it becomes product_id = 135, which flows down the relationship to order_items the way any slicer would.

Seeing it in numbers makes it stick: the same two formulas in a calculated column on products.

Choosing the table to walk

The table you hand to an iterator decides what one "item" is. AVERAGEX(order_items, ...) is an average per order line. AVERAGEX(orders, [Sales]) is an average per order, which is what "average order value" means: each order is visited, its own lines are summed through the context transition, and those order totals are averaged. Same function, different table, a different business question. Ask "what is one unit of this average?" before you type the first argument.

The same idea carries a weighted margin. Walk order_items, and for each line reach up to its product with RELATED(products[unit_cost]). The cost is multiplied by that line's quantity, so products that sell a lot count more. Averaging the margin of the 120 products would give every product the same weight, which is not what the till saw.

Measures carry an invisible CALCULATE

A reference to a measure, [Sales], behaves as if it were wrapped in CALCULATE. That is why SUMX(products, [Sales]) returns each product's own sales added up, while SUMX(products, SUM(order_items[line_total])) repeats the store total once per product. The first transitions the row into a filter; the second does not.

It also means iterators have a price. A measure inside a SUMX over a big table triggers a transition per row, which is slow and, with a hundred-thousand-row table, noticeable. Iterate over the small table (products, 120 rows) rather than the big one (order lines, over 30,000) when the answer is the same.

Where to try it in Power BI Desktop

Use Home → New measure for an iterator and Table tools → New column for a calculated column. To see what a row context does, write the same expression in both places and compare. The DAX query view lets you run EVALUATE ADDCOLUMNS(products, "x", [Sales]) and read the result as a table.

Common misconceptions

"SUM(quantity) * SUM(price) is the same as adding quantity times price"

Multiplying two totals multiplies every quantity by every price, not each line's own pair. For order 1004 it gives 1,979.82 instead of 376.15, and the gap grows with the table. Whenever a number is a product or ratio of two columns per row, it is an iterator.

"A row context filters the other tables"

It does not. Standing on a product row tells the formula which row's columns to read, nothing more. The order_items table sees every line until something creates a filter. That something is CALCULATE, or a measure reference.

"Iterators are always slower, so avoid them"

An iterator over a column expression (SUMX(order_items, quantity * unit_price)) is cheap: the storage engine handles it. The slow pattern is a measure evaluated once per row of a large table. Choose the smallest table that gives the right answer and keep the expression simple.