DAX CALCULATE Explained: Filter Context Without the Headache

📎 This article includes 2 downloadable practice files ↓

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.

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

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.

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

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

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

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:

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 ) )

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):

-- 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

New to DAX? Start with your first measures and your first Power BI report.