3.2.2. The CALCULATE Function

💡 First Principle: CALCULATE is the only DAX function that changes filter context. It evaluates an expression after adding, replacing, or removing filters, and when used in row context it converts the current row into a filter (context transition). Almost every non-trivial measure is CALCULATE in some form.

Syntax: CALCULATE(<expression>, <filter1>, <filter2>, ...). Filter arguments can be:

• Boolean expressions on one column: Product[Color] = "Red", Sales[Quantity] > 10. These overwrite existing filters on that column. CALCULATE([Total Sales], Product[Color] = "Red") returns red-product sales even if the user selected Blue, because the user's Color filter is replaced.
• Table expressions, typically FILTER(table, condition) for conditions that involve measures or multiple columns: CALCULATE([Total Sales], FILTER(Customer, [Total Sales] > 10000)). Prefer filtering columns rather than whole tables for performance.
• Filter modifiers: ALL(table or column) and REMOVEFILTERS(...) remove filters (used for percent-of-total); ALLEXCEPT(table, column) removes all filters on a table except the listed columns; ALLSELECTED respects outer filters such as slicers while ignoring the visual's own row/column filters; KEEPFILTERS(filter) intersects with existing filters instead of overwriting; USERELATIONSHIP activates an inactive relationship; CROSSFILTER changes filter direction for this calculation.

Common patterns:

Red Sales = CALCULATE ( [Total Sales], Product[Color] = "Red" )

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

% of Selected Products =
DIVIDE ( [Total Sales], CALCULATE ( [Total Sales], ALLSELECTED ( Product ) ) )

Red Sales Within Selection =
CALCULATE ( [Total Sales], KEEPFILTERS ( Product[Color] = "Red" ) )

Context transition happens when CALCULATE runs inside a row context (in a calculated column or an iterator). The current row's values become filters, so SUMX(Customer, CALCULATE([Total Sales])) evaluates each customer's sales. Any measure reference implicitly wraps CALCULATE, which is why referencing [Total Sales] in a calculated column on Customer returns that customer's sales (1.4.1).

⚠️ Exam Trap: With a slicer set to Blue, CALCULATE([Total Sales], Product[Color] = "Red") still shows red sales, because the boolean filter overwrote the slicer's Color filter. If the requirement is "show red sales only when red is among the user's selections," wrap the filter in KEEPFILTERS (which returns blank when Blue alone is selected).

Reflection Question: A matrix lists categories with a % of Total measure. Users want the percentages to sum to 100% of whatever categories they've chosen in a slicer, not of all categories. Which modifier should replace REMOVEFILTERS, and why does it behave differently?