A measure never computes in a vacuum. It computes in a filter context, the complete set of filters constraining the model at that moment. Everything acting on the cell feeds that context: the row label, the column label, every slicer, every report filter, and any filters injected by DAX itself. Picture [Total Sales] in the "North" row of a matrix that also has a "Q1" slicer engaged. Its filter context is {Region = North, Quarter = Q1}, so SUM(Sales[Amount]) sums only the fact rows that survive that slice. Move to a different row or pick a different slicer, and the same measure returns a different number. The measure is fixed and the context moves. Internalise that one sentence and you're 80% of the way to learning DAX.
Why do you need CALCULATE?
Most of the time the PivotTable hands you the filter context. You drag fields onto rows and slicers, and the context assembles itself. Often, though, you need a measure that computes in a context different from the cell it's in. Think of "sales for the North region specifically, regardless of which region's row I am in," or "this product's share of the all-products total." For that you have to change the filter context from inside the formula, and only one function does it.
That function is CALCULATE, and it's the most important function in DAX. Its signature:
CALCULATE( <expression>, <filter1>, <filter2>, … )It evaluates <expression> in a filter context modified by the filter arguments. Read it as "compute this measure, but with these filters applied." The measure [North Sales], the workhorse of Worked example 1, is the canonical shape:
North Sales := CALCULATE( [Total Sales], Region[Name] = "North" )In words, evaluate [Total Sales], but in a context forced to Region[Name] = "North". Drop [North Sales] into a grand-total cell and it returns North's sales. Drop it next to [Total Sales] in a per-region matrix and every row shows North's number, because the formula forces North whatever the row. That's how you build a "% of North" comparison.
Advertisement
Does CALCULATE override or add to existing filters?
If you remember one thing about CALCULATE, remember that it overrides filters and doesn't add to them. A boolean filter argument like Region[Name] = "North" replaces any existing filter on the Region[Name] column. It doesn't intersect with it. Say [North Sales] is in the "South" row of a region matrix. The row's context says Region = South, but CALCULATE's argument overwrites that column's filter to Region = North, so the cell returns North's sales. You don't get a blank. Beginners expect one, since South ∩ North is nothing. Experts know the override rule and expect North. Under the hood, Region[Name] = "North" is shorthand for FILTER( ALL(Region[Name]), Region[Name] = "North" ). The ALL clears the existing filter before the new one is applied, and it's the reason override is the default and intersection isn't.
When you do want intersection, meaning "keep whatever filter is already here and also require North", wrap the filter in KEEPFILTERS:
North Sales (respecting row) := CALCULATE( [Total Sales], KEEPFILTERS( Region[Name] = "North" ) )Now the South row returns blank because South and North can't both hold. Only the North row shows a number. Choosing between plain CALCULATE, which overrides, and KEEPFILTERS, which intersects, is a deliberate decision every time. Getting it wrong is common mistake #3. Keep one line in your head. CALCULATE replaces the filter on a column unless you KEEPFILTERS.