CALCULATE and filter context in DAX

CALCULATE is the most important function in DAX and the one most often misused. To use it well you need one idea: every number in a Power BI visual is computed inside a filter context.

What filter context means

Put Region on the rows of a matrix, Month on the columns, and drop in a measure. For the cell at West / September, the engine filters Store to West, filters Date to September, propagates both to Sales, and only then evaluates your expression. Change the cell and the filters change — the formula does not.

Slicers, rows, columns, page filters and the visual's own filter card all contribute to that context.

Measures vs calculated columns

	Calculated column	Measure
Evaluated	Once per row, at refresh	At query time, per visual cell
Context	Row context	Filter context
Stored	Yes, uses RAM in the model	No, computed on demand
Good for	Row-level attributes: Pack Size Band, Is Rural	Every aggregation: sums, ratios, YoY

A calculated column can reach across a relationship from the many side with RELATED:

Line MRP Value = Sales[Qty] * RELATED ( Product[MRP] )

But do not create a Total Sales column — an aggregation belongs in a measure:

Total Sales = SUM ( Sales[NetAmount] )

CALCULATE changes the filter context

CALCULATE ( <expression>, <filter1>, <filter2>, ... ) evaluates the expression after modifying the context. Each filter argument replaces the existing filter on those columns:

Premium Soap Sales =
CALCULATE ( [Total Sales], Product[Category] = "Soap", Product[Pack] = "125g" )

To remove filters instead, use ALL or the clearer REMOVEFILTERS:

Brand Share % =
DIVIDE (
    [Total Sales],
    CALCULATE ( [Total Sales], REMOVEFILTERS ( Product[Brand] ) )
)

In a matrix sliced by brand, the numerator respects the brand on the row while the denominator ignores it — giving each brand's share of the visible total. Every other filter (region, month) still applies to both.

If you want to add to the existing filter rather than replace it, wrap the condition in KEEPFILTERS:

Large Packs Only =
CALCULATE ( [Total Sales], KEEPFILTERS ( Product[Pack] IN { "500g", "1kg" } ) )

Without KEEPFILTERS, a user's slicer selection of 125g would be overridden and the number would be wrong.

Context transition

CALCULATE also converts a row context into a filter context. That is why this works inside an iterator:

Stores Above 50k =
COUNTROWS (
    FILTER ( VALUES ( Store[StoreName] ), [Total Sales] > 50000 )
)

Referencing the measure [Total Sales] implicitly wraps it in CALCULATE, so the current store row becomes a filter and each store is evaluated separately.

Common mistakes

• Writing CALCULATE ( SUM ( Sales[NetAmount] ), Sales[NetAmount] > 1000 ) — a filter argument must reference a column in a table filter, so use FILTER ( Sales, Sales[NetAmount] > 1000 ).
• Using / instead of DIVIDE, which throws an error on zero denominators instead of returning blank.
• Creating calculated columns for anything a measure can do, bloating model size and refresh time.
• Forgetting that ALL ( Sales ) clears filters coming through relationships too, not just direct ones.