pbPassingBI
/
DAX advanced 12 min

Filter context and CALCULATE

The single most important idea in DAX, and the function that manipulates it.

What you'll be able to do
  • Define filter context and where it comes from
  • Use CALCULATE to add, replace, and remove filters
  • Apply ALL, ALLSELECTED and REMOVEFILTERS correctly

What filter context is

When a visual renders a cell, it establishes a filter context — the set of filters applied to the model at that moment. It comes from the rows and columns of the visual, slicers, page and report filters, and the filter pane.

A measure is evaluated once per cell, inside that cell's filter context. This is why SUM(Sales[Amount]) returns a different number in each row of a matrix without you writing any logic.

CALCULATE modifies it

CALCULATE(<expression>, <filter1>, <filter2>, ...) evaluates the expression in a modified filter context. It is the only function that can do this, and it's the heart of DAX.

Red Sales = CALCULATE(SUM(Sales[Amount]), Product[Colour] = "Red")

By default filter arguments replace any existing filter on that same column. So even if the visual is showing Blue, this measure returns Red — the argument overrides.

To add to the existing filter instead, use KEEPFILTERS.

Removing filters

ALL(Product) removes every filter from the Product table. ALL(Product[Colour]) removes filters from just that column. REMOVEFILTERS is a clearer synonym introduced later — same behaviour, better name.

The classic use is a percent-of-total denominator:

% of Total =
DIVIDE(
    SUM(Sales[Amount]),
    CALCULATE(SUM(Sales[Amount]), REMOVEFILTERS(Product))
)

The numerator respects the visual; the denominator ignores Product filters, giving a constant total.

ALLSELECTED

ALLSELECTED removes filters from the visual itself but respects slicers and page filters. That's the difference people miss.

Use ALL when you want a true grand total regardless of anything. Use ALLSELECTED when you want the total of what the user has currently selected — a percent-of-visible-total. Choosing wrong here produces percentages that don't add to 100 and is a very common bug.

Context transition

When CALCULATE is used where a row context exists — inside an iterator like SUMX, or in a calculated column — it converts that row context into an equivalent filter context. This is context transition, and it happens automatically.

It's the reason a measure referenced inside SUMX behaves as if filtered to the current row, which is usually what you want but occasionally very surprising.

Key points
  • Filter context comes from the visual, slicers and filters; measures evaluate inside it
  • CALCULATE filter arguments replace existing filters unless wrapped in KEEPFILTERS
  • ALL ignores everything; ALLSELECTED respects slicers — pick deliberately
Check yourself