DAX CALCULATE explained: filter context without the confusion

If you learn one DAX function properly, make it CALCULATE. Almost every useful Power BI measure, from year-over-year growth to percentage of total, is built on it. The good news: once filter context clicks, the rest follows.

Start with simple measures

Always build explicit base measures first and reuse them everywhere.

Total Sales = SUM ( Sales[Amount] )
Total Orders = DISTINCTCOUNT ( Sales[OrderID] )
Average Order Value = DIVIDE ( [Total Sales], [Total Orders] )

DIVIDE returns blank instead of an error when dividing by zero, so it is always safer than the / operator.

Filter context in one sentence

Every cell in a visual is calculated with the filters that apply to it: the row and column headers, slicers, and page or report filters. That set of filters is the filter context. A measure like [Total Sales] gives a different answer in every cell because the filter context is different.

CALCULATE changes the filter context

CALCULATE evaluates an expression after adding, replacing or removing filters.

Coffee Sales =
CALCULATE ( [Total Sales], 'Product'[Category] = "Coffee" )

In a table by region, this shows coffee sales for each region, whatever category a slicer is set to, because the filter on 'Product'[Category] is replaced.

KEEPFILTERS: add, do not replace

Coffee Sales (respect slicer) =
CALCULATE ( [Total Sales], KEEPFILTERS ( 'Product'[Category] = "Coffee" ) )

Now, if the slicer is set to "Tea", the result is blank: the new filter is intersected with the existing one instead of overriding it.

Removing filters: percentage of total

% of All Products =
DIVIDE (
    [Total Sales],
    CALCULATE ( [Total Sales], REMOVEFILTERS ( 'Product' ) )
)

% of Category =
DIVIDE (
    [Total Sales],
    CALCULATE ( [Total Sales], REMOVEFILTERS ( 'Product'[ProductName] ) )
)

REMOVEFILTERS is the modern, more readable equivalent of using ALL inside CALCULATE.

Time intelligence

These functions need a proper date table marked as a date table in Power BI, as described in the star schema guide.

Sales LY =
CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )

Sales YoY % =
DIVIDE ( [Total Sales] - [Sales LY], [Sales LY] )

Sales YTD =
TOTALYTD ( [Total Sales], 'Date'[Date] )

Sales Rolling 3M =
CALCULATE (
    [Total Sales],
    DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -3, MONTH )
)

Variables make DAX readable

Sales YoY % (clear) =
VAR CurrentSales = [Total Sales]
VAR PreviousSales = [Sales LY]
RETURN
    IF ( NOT ISBLANK ( PreviousSales ), DIVIDE ( CurrentSales - PreviousSales, PreviousSales ) )

Row context and iterators

Functions ending in X, such as SUMX, loop over a table row by row. Inside that loop you have row context: you can read the current row's columns.

Revenue = SUMX ( Sales, Sales[Quantity] * Sales[UnitPrice] )

Row context does not filter anything by itself. When you call a measure inside an iterator, DAX performs context transition: it turns the current row into filters, as if the measure were wrapped in CALCULATE. That is why this works:

Customers Above £1k =
COUNTROWS (
    FILTER ( VALUES ( Customer[CustomerID] ), [Total Sales] > 1000 )
)

Checklist for reliable measures

• Build base measures first and reference them, rather than repeating logic.
• Use DIVIDE, variables and clear names.
• Use a star schema with a marked date table.
• Test each measure in a simple table visual before putting it in a chart.

Every pattern here is on the printable DAX cheat sheet.

Written by Alessandro Ecclesie Agazzi, freelance analytics engineer in London. Updated 30 September 2026.