If you understand CALCULATE, you understand most of DAX. It is also the function beginners find most confusing, because it depends on an idea called filter context. Let us build that idea first.
Filter context: what a measure "sees"
Suppose you have a measure:
Total Sales = SUM ( Sales[Amount] )Put it in a table visual with Region on rows. The same measure shows a different number in each row. Why? For the "North" row, Power BI filters the Sales table to North before calculating. That set of active filters (from rows, columns, slicers and page filters) is the filter context.
What CALCULATE does
CALCULATE evaluates an expression after changing the filter context:
CALCULATE ( <expression>, <filter 1>, <filter 2>, ... )The examples use a fictional appliance retailer, HomeGlow, with Sales, Product, Store and Date tables in a star schema.
Pattern 1: a fixed filter
AC Sales = CALCULATE ( [Total Sales], 'Product'[Category] = "Air Conditioners" )In any row of a visual, this shows sales of air conditioners only. If the visual is already filtered to a different category, this filter on the same column replaces it.
Pattern 2: several conditions
Online AC Sales =
CALCULATE (
[Total Sales],
'Product'[Category] = "Air Conditioners",
Sales[Channel] = "Online"
)Separate filter arguments are combined with AND.
Pattern 3: share of total with ALL / REMOVEFILTERS
All Region Sales = CALCULATE ( [Total Sales], REMOVEFILTERS ( Store[Region] ) )
Region Share % = DIVIDE ( [Total Sales], [All Region Sales] )REMOVEFILTERS (or ALL) removes the region filter, so the denominator is the total across all regions, while other filters (like the year slicer) still apply.
Pattern 4: keep the user's filter and add one
Premium Sales =
CALCULATE ( [Total Sales], KEEPFILTERS ( 'Product'[Tier] = "Premium" ) )KEEPFILTERS intersects with existing filters instead of replacing them.
Pattern 5: time comparisons
Sales LY = CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
Sales YoY % = DIVIDE ( [Total Sales] - [Sales LY], [Sales LY] )
Sales YTD = CALCULATE ( [Total Sales], DATESYTD ( 'Date'[Date] ) )Time intelligence functions return a set of dates, and CALCULATE uses that set as the new date filter. This needs a proper Date table marked as a date table.
Pattern 6: filter by a measure with FILTER
Simple column filters cannot use measures. For that, iterate with FILTER:
Sales From Big Stores =
CALCULATE (
[Total Sales],
FILTER ( VALUES ( Store[StoreID] ), [Total Sales] > 1000000 )
)Use FILTER over a small table (like VALUES of a column), not the whole fact table, to keep it fast.
Common mistakes
- Expecting a filter on one column to combine with a visual filter on the same column. By default, it replaces it.
- Using
/instead ofDIVIDE, and getting errors when the denominator is zero. - Writing
FILTER ( Sales, ... )over a huge fact table when a simple column filter would do. - No Date table, so time intelligence returns blanks or wrong values.
Practise in a real model
The best way to learn CALCULATE is to need it. Our motor claims Power BI project asks for SLA percentages, month-on-month changes and shares of total, all of which rely on these patterns.
Practise what you just read
Reading helps; writing the code yourself is what sticks. Start here: