CALCULATE and filter context, worked through a waiting list

Most wrong DAX numbers aren’t syntax errors. The measure runs, returns something plausible, and answers a slightly different question because a filter was replaced when it should have been kept, or removed from the wrong column. Once you can say which filters are active in each cell, most DAX stops being mysterious.

This article works through that with one small, synthetic example: a weekly snapshot of an RTT waiting list for an invented acute hospital, taken on 2 August 2026. The data comes from a seeded generator in projects/article-examples/power-bi/, and every number in the tables below was produced by that code. The model looks like this:

'Waiting List'   one row per specialty, site and completed weeks waiting
  [Specialty Key]  ->  'Specialty'[Specialty Key]    Specialty, Division
  [Site Key]       ->  'Site'[Site Key]              Site
  [Weeks Waiting], [Pathways]

Seven specialties sit in two divisions: General surgery, Trauma and orthopaedics (T&O), ENT and Ophthalmology in Surgery, and General medicine, Cardiology and Dermatology in Medicine. The base measure is:

Waiting List := SUM ( 'Waiting List'[Pathways] )

Two kinds of context

Filter context is the set of filters in force when a measure is evaluated. The rows and columns of the visual, slicers, the Filters pane and CALCULATE all add filters. A filter on a dimension column flows along each relationship from the one side to the many side, so filtering 'Specialty'[Specialty] filters the waiting list.

Row context means “the current row” while DAX walks through a table: in a calculated column, or inside an iterator such as SUMX or FILTER. A row context lets you read column values from that row. It does not filter anything.

That last sentence is the source of a classic surprise. Add two calculated columns to the Specialty table:

Pathways (no CALCULATE) = SUM ( 'Waiting List'[Pathways] )
Pathways = CALCULATE ( SUM ( 'Waiting List'[Pathways] ) )

The first column returns the whole list on every row, because the row context on Specialty never reaches the Waiting List table. The second wraps the sum in CALCULATE, which turns the current row into a filter on Specialty. That is context transition. It also happens whenever you reference a measure inside a row context, because DAX wraps every measure reference in an implicit CALCULATE.

What CALCULATE does, in order

CALCULATE doesn’t evaluate its arguments left to right. The expression comes last:

1. Evaluate the filter arguments in the original filter context.
2. If there is a row context, turn it into a filter context (context transition).
3. Apply the modifiers: REMOVEFILTERS, the ALL family, USERELATIONSHIP, CROSSFILTER.
4. Apply the filter arguments. Each one overwrites any existing filter on the same columns, unless it’s wrapped in KEEPFILTERS.
5. Evaluate the expression in the new filter context.

Step 4 explains most surprises. A Boolean filter such as 'Specialty'[Specialty] = "Trauma and orthopaedics" is shorthand for FILTER ( ALL ( 'Specialty'[Specialty] ), ... ): it clears that column and sets a new value. It only touches the columns it names.

Replacing versus KEEPFILTERS

Three measures in a table visual with Specialty on the rows:

T&O List :=
CALCULATE ( [Waiting List], 'Specialty'[Specialty] = "Trauma and orthopaedics" )

T&O List (KEEPFILTERS) :=
CALCULATE (
    [Waiting List],
    KEEPFILTERS ( 'Specialty'[Specialty] = "Trauma and orthopaedics" )
)

Surgical List :=
CALCULATE ( [Waiting List], 'Specialty'[Division] = "Surgery" )

T&O List shows 5,884 on every row because its filter replaces the row’s filter on the same column. KEEPFILTERS intersects instead, so only the T&O row survives. Surgical List filters a different column, Division, so nothing is replaced: the row’s specialty filter and the division filter both apply, and the medical specialties go blank.

Neither behaviour is wrong. A fixed benchmark column wants replacement; a filter that should respect the visual wants KEEPFILTERS. Decide which you mean and write it that way. Microsoft’s own guidance also prefers KEEPFILTERS ( column = value ) to FILTER ( 'Table', ... ) as a CALCULATE argument, because a column predicate is cheaper than iterating a whole table.

Removing filters: REMOVEFILTERS, ALL and ALLEXCEPT

Shares of a total are where filter removal goes wrong most often. Four attempts, again with only Specialty on the rows:

% of All :=
DIVIDE ( [Waiting List], CALCULATE ( [Waiting List], REMOVEFILTERS ( 'Specialty' ) ) )

-- Mistake: clears a column the visual isn't filtering
% (fact key) :=
DIVIDE (
    [Waiting List],
    CALCULATE ( [Waiting List], REMOVEFILTERS ( 'Waiting List'[Specialty Key] ) )
)

-- Mistake: ALLEXCEPT can only keep a filter that already exists
% of Division (ALLEXCEPT) :=
DIVIDE (
    [Waiting List],
    CALCULATE ( [Waiting List], ALLEXCEPT ( 'Specialty', 'Specialty'[Division] ) )
)

-- Fix: clear the dimension, then put the current division back
% of Division :=
DIVIDE (
    [Waiting List],
    CALCULATE (
        [Waiting List],
        REMOVEFILTERS ( 'Specialty' ),
        VALUES ( 'Specialty'[Division] )
    )
)

The fact-key version returns 100% everywhere. The visual filters 'Specialty'[Specialty], and that filter still reaches the fact table through the relationship, whatever you do to 'Waiting List'[Specialty Key]. Remove filters from the column the visual actually uses, or from the whole dimension.

The ALLEXCEPT version quietly returns the share of the whole list. With no Division on the visual there is no Division filter to keep, so ALLEXCEPT removes everything. It only works when Division happens to be on the rows too, which makes the measure depend on the layout. REMOVEFILTERS followed by VALUES ( 'Specialty'[Division] ) works either way: VALUES is evaluated in the original context (step 1), so it captures the current row’s division before the dimension is cleared.

Two more points on this family:

• ALL and REMOVEFILTERS do the same job as CALCULATE modifiers. ALL can also return a table; REMOVEFILTERS can’t, which makes intent obvious. Use REMOVEFILTERS when you mean “remove”.
• Removing filters from a fact table goes further than it looks. REMOVEFILTERS ( 'Waiting List' ) clears filters on the table’s expanded form, which includes the dimensions it relates to, so a Site slicer stops working inside that CALCULATE.

A measure is not a column

A common first attempt at “% within 18 weeks” is a calculated column on Specialty. Here it is next to the equivalent measure, with a slicer set to North site:

-- Calculated column on 'Specialty'
% Within 18 Weeks Column =
DIVIDE (
    CALCULATE ( SUM ( 'Waiting List'[Pathways] ), 'Waiting List'[Weeks Waiting] < 18 ),
    CALCULATE ( SUM ( 'Waiting List'[Pathways] ) )
)

-- Measure
% Within 18 Weeks :=
DIVIDE (
    CALCULATE ( [Waiting List], 'Waiting List'[Weeks Waiting] < 18 ),
    [Waiting List]
)

The column was computed once, at refresh, for both sites together. It never sees the slicer, so every row shows the all-site figure. The total is worse: the column’s default summarisation is Sum, which adds seven percentages to get 520.9%. The measure recomputes the ratio in every cell, including the total.

My rule: anything that should respond to a slicer, and every ratio, average or percentage, is a measure. Calculated columns are for attributes you slice or group by, such as a wait band, and those are usually better built upstream in Power Query or SQL.

Iterators and the cost of context transition

Iterators such as SUMX and AVERAGEX evaluate an expression once per row of a table. When that expression is a measure, each row triggers a context transition. Over a small dimension, that’s what you want:

Avg List per Specialty := AVERAGEX ( VALUES ( 'Specialty'[Specialty] ), [Waiting List] )

Seven transitions cost next to nothing. The trouble starts when the iterated table is a fact table. Suppose an Admissions fact at spell grain, with the spell ID removed to save memory (the VertiPaq article explains why you’d do that):

Bed Days := SUM ( Admissions[Length of Stay] )

-- Wrong: a context transition on every row of the fact table
Bed Days (iterated) := SUMX ( Admissions, [Bed Days] )

-- Right: read the column from the row context
Bed Days (iterated) := SUMX ( Admissions, Admissions[Length of Stay] )

The wrong version has two problems. The first is cost: one transition per admission, 4,312 of them for July 2026 alone, where a plain SUM is a single aggregation over one column. The second is correctness. Context transition filters on every column of the current row, and without a unique key, two admissions on the same day with the same specialty, site, demographics, method and length of stay are identical rows. Each one’s transition picks up both, so both get counted twice.

Those duplicates are legitimate: different patients, identical attributes. The error is 1.6%, small enough to get past a glance and big enough to fail a reconciliation. Inside a row-by-row iterator, reference columns, not measures, unless you actually want a transition per row.

What to check in any CALCULATE

When a number looks off, I go through the same four questions:

• Which columns does each filter argument touch, and does it replace or intersect the filter already on them?
• Does each REMOVEFILTERS or ALL clear the column the visual uses, and no more than intended?
• Is there a row context that will transition, and is the iterated table small and unique?
• Should this be a measure rather than a column?

The same ideas carry into the rest of this series: the star schema article covers the model these filters flow through, the time intelligence article is CALCULATE with date filters, and for RTT measures specifically, see DAX for waiting-list KPIs and the RTT waiting list semantic model project.