If DAX has one function that separates beginners from confident report builders, it is CALCULATE. Once you understand what it does to filters, most DAX becomes readable.
In this article
Filter context in one picture
Every number in a Power BI visual is calculated under a set of filters: the row of the table visual, the slicers, the page filters, cross-filtering from other visuals. That set is the filter context. A measure like
“`text
Total Sales = SUM ( Sales[Amount] )
“`
simply sums whatever rows survive those filters. In the “North” row of a matrix, only North rows survive.
What CALCULATE does
CALCULATE ( expression, filter1, filter2, … ) evaluates the expression after changing the filter context:
- A filter on a column replaces any existing filter on that same column.
- Filters on other columns are left alone.
- All the filter arguments are combined with AND.
“`text
North Sales = CALCULATE ( [Total Sales], Regions[Region] = “North” )
“`
Put this measure in a matrix with regions in rows: every row shows North’s sales, because the filter on Region is replaced. That surprises everyone once — and then it is the key to everything below.
Pattern 1: share of total
“`text
Share of Region Total =
DIVIDE ( [Total Sales], CALCULATE ( [Total Sales], REMOVEFILTERS ( Regions ) ) )
“`
REMOVEFILTERS(Regions) (or ALL(Regions)) clears region filters for the denominator only, so each row is divided by the grand total.
Pattern 2: share within a parent
“`text
Share of Category =
DIVIDE ( [Total Sales], CALCULATE ( [Total Sales], REMOVEFILTERS ( Products[Product] ) ) )
“`
Only the product filter is removed, so a product is compared with its category total — useful in a matrix with Category → Product.
Pattern 3: respect the user’s selection with KEEPFILTERS
“`text
Retail Sales (respect slicer) =
CALCULATE ( [Total Sales], KEEPFILTERS ( Customers[Type] = “Retail” ) )
“`
Without KEEPFILTERS, a slicer choosing “Wholesale” would be overwritten and you’d still see Retail. With it, the two filters intersect, and Wholesale correctly gives blank.
Pattern 4: time intelligence
Create a proper Calendar table (every date, no gaps), relate it to your fact table, and mark it as a date table. Then:
“`text
Sales YTD = CALCULATE ( [Total Sales], DATESYTD ( ‘Calendar'[Date], “3/31” ) ) — Indian FY ending 31 March
Sales LY = CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( ‘Calendar'[Date] ) )
Sales vs LY % = DIVIDE ( [Total Sales] – [Sales LY], [Sales LY] )
Rolling 3M = CALCULATE ( [Total Sales], DATESINPERIOD ( ‘Calendar'[Date], MAX ( ‘Calendar'[Date] ), -3, MONTH ) )
“`
"3/31", otherwise YTD resets in January.
Row context vs filter context
Calculated columns and iterators (SUMX, FILTER) run row by row — that is row context, and it does not filter anything by itself. CALCULATE turns the current row into a filter (context transition):
“`text
— calculated column in Customers: each customer’s total sales
Customer Sales = CALCULATE ( SUM ( Sales[Amount] ) )
“`
Without CALCULATE, SUM(Sales[Amount]) in a Customers column would return the grand total on every row.
Common mistakes
| Symptom | Cause | Fix |
|---|---|---|
| Same number on every row | Filter replaced the row’s filter | Use KEEPFILTERS, or filter a different column |
| YTD resets in January | Default year-end 31 Dec | DATESYTD(…, “3/31”) |
| Time intelligence returns blank | No proper date table or gaps in dates | Build a continuous Calendar and mark it |
| Slow report | FILTER over a whole table inside CALCULATE | Filter columns directly: Table[Col] = "x" |
New to DAX? Start with your first measures and your first Power BI report.
Stuck on a step? Ask a question and the AI answers using this article.