CALCULATE and Context Transition

Phase 1 left you with a working model of row context and filter context: row context is "which row am I standing on," filter context is "which rows survived the slicers, the visual, and the filter pane." CALCULATE is the one function that lets you reach into filter context and change it mid-formula. It is the single most powerful function in DAX, and also the one that produces the most "wait, why is this number different" moments. Once you can predict what CALCULATE does before you run it, most of the mystery in DAX disappears.

What CALCULATE actually is

The mental model first. Every other DAX function evaluates inside the filter context it's handed. CALCULATE is different: it evaluates an expression after modifying the filter context around it. Think of filter context as a set of instructions taped to the wall - "only look at rows where Region = West, Year = 2024." CALCULATE lets a measure walk up, tear off one of those instructions, tape up a new one, and only then read the wall.

CALCULATE(<expression>, <filter1>, <filter2>, ...)

The first argument is what to compute - almost always another measure or an aggregation like SUM(Sales[Amount]). Everything after that is a filter argument, and filter arguments are where the actual power (and the actual confusion) lives.

Filter arguments: replace, don't intersect

The single most important thing to internalize about CALCULATE's filter arguments is this:

A filter argument does not narrow the existing filter on that column - it replaces it entirely.

This trips people up constantly because it's not how an AND in a WHERE clause behaves. Watch it happen:

Sales West =
CALCULATE(
    SUM(Sales[Amount]),
    Sales[Region] = "West"
)

Drop this measure into a table visual sliced by Sales[Region], and every single row shows the same number - the total West sales - regardless of what region that row of the table represents. That's not a bug. Sales[Region] = "West" didn't add to the visual's existing filter on Region; it replaced it outright. The row for "East" asked "what are West sales," and CALCULATE answered straight, exactly as asked.

This is exactly why CALCULATE is powerful: it's how you build a measure that deliberately ignores what the user clicked. It's also exactly why it's dangerous: if you forget this rule, you write measures that silently ignore slicers you expected them to respect.

Context transition: the part nobody tells you clearly

Here's the idea that separates people who can write CALCULATE from people who can predict what it does: CALCULATE doesn't just add filters - it also converts any row context in scope into an equivalent filter context. This is called context transition, and it only happens because of CALCULATE (or a handful of things that implicitly wrap CALCULATE, like measure references inside SUMX).

Recall from phase 1: row context exists inside iterators like SUMX and inside calculated columns - it's "I'm standing on this one row." Filter context is what a measure actually reads. Normally those two never talk to each other. CALCULATE is the bridge.

Here's the pattern that makes this concrete - a calculated column that calls a measure:

-- Calculated column on the Products table
Product Total Sales = CALCULATE(SUM(Sales[Amount]))

You're inside a calculated column, so you have row context - you're standing on one product row, say "Blue Widget." SUM(Sales[Amount]) has no idea what a "row context" is; it only reads filter context, and by default a calculated column has no filter context at all (it would sum the whole Sales table for every single row, giving every product the grand total). But because you wrapped it in CALCULATE, DAX performs context transition: it looks at the row you're standing on, finds every column value on that row ("Blue Widget"), and injects an equivalent filter - Products[Name] = "Blue Widget" - into filter context before evaluating. That filter sits on the Products table, and the Products -> Sales relationship carries it across to restrict SUM(Sales[Amount]) to that product's rows. The row you were standing on becomes a filter. That's the whole trick.

Without the CALCULATE wrapper, SUM(Sales[Amount]) in that same column would just be the grand total repeated on every row, because there'd be no filter context to shrink it. This is why you'll see experienced DAX authors reach for CALCULATE even around something that looks like it needs no filter arguments at all - they're not filtering, they're transitioning.

The same thing happens inside SUMX and other iterators whenever the expression being iterated calls a measure:

Total With Tax =
SUMX(
    Sales,
    Sales[Amount] * (1 + RELATED(Products[TaxRate]))
)

No context transition needed here because RELATED and the multiplication just read row context directly - no measure call, no CALCULATE, no transition. But the moment you swap in a measure reference:

Total With Tax v2 =
SUMX(
    Sales,
    [Unit Tax Measure]   -- implicitly wrapped in CALCULATE
)

Every call to [Unit Tax Measure] inside that iteration silently performs context transition, turning "the row I'm on" into "a one-row filter," before the measure evaluates. This is invisible in the formula bar and is one of the most common sources of performance surprises (a transition per row, times a million rows) and correctness surprises (the measure now sees a filter it didn't expect) once you get to phase 5 on performance.

ALL, ALLEXCEPT, ALLSELECTED: controlling what survives

CALCULATE's filter arguments aren't limited to conditions like Sales[Region] = "West". They can be table-returning functions that remove filters entirely, which is how you build "percent of total" and "compare to the unfiltered whole" measures.

ALL(table_or_column) removes every filter on the given table or column, ignoring slicers, visual filters, everything.

Pct of All Sales =
DIVIDE(
    SUM(Sales[Amount]),
    CALCULATE(SUM(Sales[Amount]), ALL(Sales))
)

The denominator recalculates total sales as if no filter existed on any Sales column - the grand total for this single-table model - while the numerator still respects whatever's currently filtered. Divide the two and you get a real percent-of-total. One caveat for star schemas: ALL(Sales) only clears filters that sit on Sales's own columns; a slicer on a related dimension table like Date still reaches Sales through the relationship, so you'd add ALL on that table too for a true grand total.

ALLEXCEPT(table, column1, column2, ...) is the scalpel version: remove all filters on the table except the ones on the columns you name.

Pct of Region =
DIVIDE(
    SUM(Sales[Amount]),
    CALCULATE(SUM(Sales[Amount]), ALLEXCEPT(Sales, Sales[Region]))
)

This clears filters on Product, Date, Customer - everything - but keeps the Region filter standing. So a row for "West / Blue Widget" gets compared against the total for all of West, not the grand total. ALLEXCEPT is ALL with an escape hatch for the one or two columns you still want respected.

ALLSELECTED(table_or_column) is the one that confuses people because it sounds like ALL but behaves differently: it removes filters coming from inside the visual (like a filter on a column added within the chart itself) while still respecting filters from outside the visual - slicers, the filter pane, page filters. It answers "what does the rest of what the user selected look like," not "what does everything look like." Use it for things like "percent of the currently sliced total" in a table that itself breaks that total down further - so a subtotal row correctly sums to 100% of what's visibly selected, not 100% of the entire database.

A rough way to keep these straight: ALL = "pretend nothing is filtered, anywhere." ALLEXCEPT = "pretend nothing is filtered, except these columns." ALLSELECTED = "pretend this visual isn't adding its own filters, but respect everything the user picked outside it."

Putting it together: a real pattern

A common real request - "sales for the current row's region, but always compare against last year regardless of what's in the visual" - combines everything above:

Region Sales LY =
CALCULATE(
    SUM(Sales[Amount]),
    SAMEPERIODLASTYEAR('Date'[Date]),
    ALLEXCEPT(Sales, Sales[Region])
)

Read it the way CALCULATE actually executes it: start from the current filter context, replace the date filter with "same period, one year back," clear every other filter except Region, then sum. Nothing here is magic once you separate "what filter context currently exists," "which filter arguments replace pieces of it," and whether a context transition is quietly happening because you're inside an iterator or a calculated column.

Recap

1. CALCULATE modifies filter context and then evaluates an expression inside the modified context.
2. Filter arguments replace the existing filter on that column, they don't intersect with it - this is the single biggest source of "why is every row the same number" bugs.
3. Context transition turns row context into filter context, and happens automatically inside CALCULATE and inside any measure call sitting in an iterator or calculated column - even with no visible filter arguments.
4. ALL clears filters entirely, ALLEXCEPT clears all but the named columns, ALLSELECTED clears only what the visual itself added, keeping outside selections.
5. Predicting a CALCULATE result means tracking three things at once: what filter context existed before, what the filter arguments replace, and whether a context transition just happened underneath you.

Quick check

Test yourself on the two ideas that cause the most confusion here - that filter arguments replace rather than intersect, and that context transition turns a row into a filter:

[
  {
    "q": "A table visual is sliced by `Sales[Region]`. You add a measure `CALCULATE(SUM(Sales[Amount]), Sales[Region] = \"West\")`. What does the East row show?",
    "choices": [
      "Zero, because East doesn't match \"West\"",
      "West's total sales, because the filter argument replaced the visual's Region filter instead of intersecting with it",
      "East's total sales, unaffected, because CALCULATE only adds filters on columns not already filtered",
      "An error, because you can't filter a column that's already sliced by the visual"
    ],
    "answer": 1,
    "explain": "A CALCULATE filter argument overwrites the existing filter on that column rather than combining with it, so every row - including East - ends up asking for West's total."
  },
  {
    "q": "A calculated column on the Products table is `CALCULATE(SUM(Sales[Amount]))`, with no filter arguments at all. Why doesn't every product just show the grand total?",
    "choices": [
      "Calculated columns automatically filter by whatever column they're defined on",
      "CALCULATE performs context transition, converting the row you're standing on into a filter that restricts SUM to that product",
      "SUM ignores rows from other products by default, with or without CALCULATE",
      "It does show the grand total on every row - that's expected for calculated columns"
    ],
    "answer": 1,
    "explain": "CALCULATE always triggers context transition: it takes every column value on the current row and injects it as a filter before evaluating, which is why wrapping SUM in CALCULATE is enough to scope it per-row even with zero filter arguments."
  },
  {
    "q": "Inside `SUMX(Sales, Sales[Amount] * RELATED(Products[TaxRate]))`, does a context transition happen on each row?",
    "choices": [
      "Yes, every iterator triggers context transition regardless of what's inside it",
      "No, because RELATED and the multiplication read row context directly - transition only happens when a measure call sits inside the iteration",
      "Yes, because SUMX always wraps its expression in CALCULATE",
      "No, because context transition only applies to calculated columns, never to iterators"
    ],
    "answer": 1,
    "explain": "RELATED just follows a relationship using row context, no measure call involved, so nothing triggers CALCULATE's context transition - swap in a measure reference instead and every row would silently transition."
  }
]

← Phase 1: Row Context vs Filter Context · Phase 3: Common DAX Patterns →