What this post covers

How iterator functions (SUMX, AVERAGEX, MAXX, etc.) bring row context back into a measure, what happens when they sit inside CALCULATE, and the mental model that makes the whole series click into place.

The setup: a measure that ignores rows

A measure runs in filter context. That's been the spine of this series. SUM([Sales Amount]) doesn't iterate; it asks "what's the filtered total of Sales Amount given the current filters?" and returns it.

This is fine when you want a total. It's a problem when the total isn't a sum of column values, when you actually need to do something to each row before adding the results.

Classic example: weighted average price. You can't average [Price], because each line item sells a different quantity. You need (quantity × price) per line, summed, divided by total quantity. The "per line" bit doesn't exist in pure filter context. There are no rows to operate on, only filtered aggregations.

That's where iterators come in.

What iterators actually do

SUMX, AVERAGEX, MAXX, MINX, and the rest are sometimes called "X functions". They all take two arguments: a table, and an expression evaluated in the row context of each row of that table.

vba
Weighted Avg Price =
DIVIDE(
    SUMX(Sales, Sales[Quantity] * Sales[Price]),
    SUM(Sales[Quantity])
)

The mechanism, step by step:

  1. The measure is invoked in some filter context (whatever filters are active in the visual).
  2. SUMX takes the Sales table - filtered by that context - and iterates over each row.
  3. For each row, it creates a row context, evaluates Sales[Quantity] * Sales[Price], and remembers the result.
  4. When all rows are done, it sums the results.
  5. SUM(Sales[Quantity]) runs in pure filter context (no iteration).
  6. DIVIDE returns the ratio.

The key move: SUMX creates row context. It doesn't borrow it from anywhere. This is why iterators are how you bring row context back into a measure that otherwise lives entirely in filter context.

The trap: SUMX inside CALCULATE

This is where the series has been heading. Watch what happens when you wrap an iterator in CALCULATE.

vba
High Margin Sales =
CALCULATE(
    SUMX(Sales, Sales[Quantity] * Sales[Price]),
    Sales[Margin] > 0.3
)

What's actually happening, in order:

  1. CALCULATE evaluates the filter argument first. Sales[Margin] > 0.3 is converted into a filter on the Sales table.
  2. The filter context is modified: Sales is now restricted to rows where Margin > 0.3.
  3. SUMX runs against the modified Sales table, iterating only rows that passed the filter.
  4. Each iteration creates its own row context. The CALCULATE filter has no effect on the row context itself, only on which rows SUMX gets to see.

That distinction is the thing. CALCULATE shapes the set of rows. The iterator shapes what happens per row. They operate at different layers.

If you forget that, you write things like this:

vba
-- Doesn't do what you think
Wrong =
SUMX(Sales,
    CALCULATE(Sales[Quantity] * Sales[Price], Sales[Margin] > 0.3)
)

This one looks similar but behaves differently. Here, CALCULATE runs inside the iteration. Per row. Context transition kicks in (covered in Part 2), and the row context becomes a filter context. Then the CALCULATE applies Sales[Margin] > 0.3 on top of that. For a single row, "Margin > 0.3" either matches that one row or doesn't. The expression becomes either the row's quantity × price or a blank.

It's not wrong, exactly. It's doing something completely different to what the surface reading suggests.

The pattern that ties it all together

Here's the mental model that closes out the series:

  • Filter context decides which rows are available.
  • Row context decides what's currently in scope at a single-row level.
  • Iterators create row context inside a filter context.
  • CALCULATE modifies filter context.
  • Context transition (CALCULATE without an explicit filter, evaluated inside an iterator) converts the current row context into a filter context.

Read that list in order. Every DAX measure you'll ever write is some combination of those five moves. The reason DAX feels confusing isn't that there are too many concepts, there are five. The reason it feels confusing is that the same function (CALCULATE) can play radically different roles depending on whether it's sitting inside an iterator or not.

A diagnostic when things go sideways

When a measure doesn't return what you expect, ask the questions in this order:

  1. What filter context is the measure running in? (Look at the visual, slicers, and any filters.)
  2. Is there an iterator? If yes, what table is it iterating, and what filters apply to that table?
  3. Is there a CALCULATE? If yes, what does its filter argument do, and where does that argument sit relative to any iterators?
  4. Is context transition happening? (Look for naked column references or measures inside an iterator.)

If you can answer those four questions, the measure either makes sense or you've found the bug.

Where this series leaves you

Four posts in, you've got the full picture: row context (Part 1), context transition (Part 2), filter context (Part 3), and iterators (today). Every DAX measure is a composition of these. There are no more layers. There's more complexity in how you arrange the layers you already understand.

Frequently Asked Questions

What's the difference between SUMX and SUM in DAX?

SUM aggregates a single column in filter context - it doesn't iterate. SUMX iterates over a table, creating row context for each row, evaluates an expression per row, and sums the results. Use SUMX when the per-row expression involves more than one column or a calculation that can't be done at the column level.

Does SUMX always create row context?

Yes. Every iterator function (SUMX, AVERAGEX, MAXX, MINX, FILTER, ADDCOLUMNS, etc.) creates a row context for each row of the table it's iterating. That row context is independent of any filter context that's also active.

Why does my SUMX inside CALCULATE return unexpected numbers?

CALCULATE modifies filter context before the iterator runs, so the iterator sees a filtered version of the table. If the filter argument behaves differently than expected, the iterator is operating on a different set of rows than you assumed. Step through the CALCULATE's filter argument first, then trace what SUMX sees.

What is context transition in DAX?

Context transition is what happens when CALCULATE, or any measure (since a measure is implicitly a CALCULATE), is evaluated inside a row context. The row context is converted into an equivalent filter context, restricting filter context to the current row's values. This is covered in detail in Part 2 of this series.

Can you nest iterator functions in DAX?

Yes. Each iterator creates its own row context, and nested iterators stack row contexts (the inner one inherits the outer one). This is powerful but easy to misread - if you find yourself nesting more than two iterators, it's worth checking whether a single CALCULATE plus a SUMMARIZE would be clearer.

Tags // DAX SUMX Row Context Filter Context Measures Power BI Iterator Functions CALCULATE