Filter context and what CALCULATE changes

The idea

A DAX measure is one formula, but a report asks it many questions. A matrix with six category rows, a total and two slicers evaluates Sales seven times, and each time the measure sees different rows of the data. The set of filters a single evaluation runs under is its filter context.

You never pass the filters in yourself. The cell does it: its row and column headers, every slicer on the page, the Filters pane and any cross-highlighting all contribute. CALCULATE is the one function that lets a measure change that set on purpose.

Why it exists

Picture the same report without filter context. You would need a Lighting Sales measure, a Furniture Sales measure, a 2025 Lighting web Sales measure, and so on for every combination a reader can click. Spreadsheets work that way: each SUMIFS spells out its own conditions.

Power BI turns it around. You define what a number means once (Sales adds up line_total) and the report decides which rows it is about. That is why one measure works in a card, a matrix and a chart without changes, and why slicers "just work".

The cost is that the rows behind a number are invisible. When a figure looks wrong, the formula is rarely the problem. The filter context is, and you can only fix it if you can picture it.

How it works under the hood

Evaluating one cell happens in a fixed order.

1. Collect the filters. Everything around the cell adds a filter on one column: the row header on products[category], the Year slicer on 'Date'[Year], the Channel slicer on orders[channel]. Filters on different columns combine with AND.
2. Put each filter on its own table, the one that owns the column.
3. Let the filters flow along the relationships, from the one side to the many side, until they reach the table the measure reads. In the Harbor & Pine model every path ends at order_items.
4. Evaluate the measure on the rows that survived every filter.

Step 3 explains a lot of "wrong" numbers. With single-direction relationships a filter never flows from many to one. A category filter thins out order_items but leaves orders untouched, which is why "orders per category" needs DISTINCTCOUNT(order_items[order_id]) and not COUNTROWS(orders).

Here is the same cell as actual rows. The filters remove rows one after another; the sum only runs at the end.

What CALCULATE does

CALCULATE(expression, filter1, filter2, ...) runs in three moves:

1. Take a copy of the current filter context (the cell's filters).
2. Apply each filter argument to the copy. If the column is not filtered yet, the filter is added. If the column is already filtered, the new filter replaces the old one. ALL, ALLEXCEPT and REMOVEFILTERS remove filters instead.
3. Evaluate the expression in the modified copy. The original context is untouched, so the neighbouring cells and the other measures are unaffected.

The "replaces" rule is the one that surprises people, and it is exactly what shares of total rely on: CALCULATE([Sales], REMOVEFILTERS(products[category])) gives every category row the all-category total to divide by.

When you want a filter argument to narrow the existing filter rather than overwrite it, wrap it in KEEPFILTERS. Then the web row of an App Sales measure would be blank, because no order is both web and app.

Row context is a different thing

A calculated column, or an iterator like SUMX, walks a table one row at a time. That "current row" is the row context, and it is not a filter. Inside a calculated column on products, the expression SUM(order_items[line_total]) returns the grand total on every row, because nothing is filtering order_items: the current product is only a row being visited.

CALCULATE bridges the two. Wrapped in CALCULATE, the current row becomes a filter (this is called context transition), so CALCULATE(SUM(order_items[line_total])) in that column gives each product its own sales. A measure reference like [Sales] has an invisible CALCULATE around it, which is why SUMX(products, [Sales]) works and SUMX(products, SUM(order_items[line_total])) repeats the total.

Seeing the filter context in Power BI Desktop

The practice matrix on this site shows you the filters of each cell. In Power BI Desktop you can read them too: hover a cell for its tooltip, open the Filters pane for visual, page and report filters, and use Optimize → Performance analyzer → Copy query to see the exact filters the visual sends to the model. Format → Edit interactions shows which slicers reach which visuals. When a number surprises you, list its filters there before you touch the formula.

Common misconceptions

"The Total row adds up the rows above it"

The Total row is the measure evaluated again, with the row header's filter missing. For a plain sum that equals the sum of the rows. For an average, a distinct count or a ratio it usually does not: the total of "average order value" is the average over all orders, not the sum of six averages.

"A filter on any table reaches every other table"

Filters move along relationships, one side to many side, and stop there unless a relationship is set to both directions. A slicer on products never filters customers. If a visual ignores a slicer, look at the arrows in the model view before you look at the DAX.

"CALCULATE's filter is added to the row's filter"

Only when they are on different columns. On the same column the argument wins and the row's filter is dropped, which is how "the same number on every row" happens. Use KEEPFILTERS when you mean "and".