Filter context and row context, explained

Almost every question about “why does my DAX return that number?” comes down to context. DAX evaluates every formula in a context, and there are two kinds: filter context and row context. They sound similar, but they do different jobs.

Filter context: which rows are visible

Filter context is the set of filters in force when a measure is calculated. It comes from everywhere around the number:

• the row and column headers of a table or matrix visual;
• the axis of a chart;
• slicers and the Filters pane;
• filters added by CALCULATE.

A measure such as Total Sales = SUM(Sales[Revenue]) is written once, but it returns a different value in every cell, because each cell has a different filter context. In the cell for Canada, only Canada’s rows are visible to SUM.

Row context: which row you are on

Row context exists when DAX is working through a table one row at a time. You get a row context in two places:

• calculated columns, which are evaluated once for every row of their table;
• iterator functions such as SUMX, AVERAGEX, FILTER and RANKX, which evaluate an expression for every row of the table you give them.

In a row context you can refer to a column directly, such as Sales[Quantity] * Sales[Unit Price], and DAX knows which row’s values to use.

The key difference

A row context does not filter anything. It only says which row you are on. This is the source of the most common surprise in DAX:

Sales Wrong = SUMX(VALUES(Sales[Country]), SUM(Sales[Revenue]))

For each country, SUM(Sales[Revenue]) ignores the row context and returns the total for all countries. With four countries, the result is four times the grand total.

Context transition

CALCULATE turns the current row context into an equivalent filter context. This is called context transition:

Sales Right = SUMX(VALUES(Sales[Country]), CALCULATE(SUM(Sales[Revenue])))

Now each country’s row becomes a filter on Country before SUM runs, and the formula returns the expected total.

Context transition also happens automatically whenever you reference a measure, because every measure reference is wrapped in an implicit CALCULATE. That is why SUMX(VALUES(Sales[Country]), [Total Sales]) works without writing CALCULATE yourself.

How to tell which context you are in

When a result looks wrong, ask two questions: which rows are visible to this aggregation, and is there a row context that should have become a filter?

Sources

• DAX overview: context, Microsoft Learn
• CALCULATE function, Microsoft Learn
• SUMX function, Microsoft Learn