5.2 The CALCULATE Function

Quick Answer: CALCULATE modifies filter context before evaluating an expression, the only DAX function that does so directly. CALCULATE(SUM(Sales[Amount]), Products[Color] = "Red") sums Amount but only for Red products, regardless of the current color filter. ALL() removes filters; KEEPFILTERS() preserves them.

Why CALCULATE Is Critical

CALCULATE is the single most important DAX function because it is the only way to modify filter context inside a measure. Nearly every advanced pattern, time intelligence, percent-of-total, conditional aggregation, uses it.

How CALCULATE Works

1. It takes the current filter context (slicers, visuals, report filters).
2. It modifies that context with the supplied filters.
3. It evaluates the expression in the modified context.

Red Revenue = CALCULATE(SUM(Sales[Amount]), Products[Color] = "Red")
Revenue 2026 = CALCULATE(SUM(Sales[Amount]), 'Date'[Year] = 2026)
Red Revenue 2026 = CALCULATE(SUM(Sales[Amount]), Products[Color] = "Red", 'Date'[Year] = 2026)

Multiple filters in one CALCULATE combine with AND. By default, a CALCULATE filter on a column overrides any existing filter on that same column.

Filter Modifiers

ALL() and REMOVEFILTERS()

ALL() removes filters from a table or columns; REMOVEFILTERS() is the same behavior with a clearer name when used as a CALCULATE modifier.

Total Revenue All = CALCULATE(SUM(Sales[Amount]), ALL(Sales))
Revenue All Colors = CALCULATE(SUM(Sales[Amount]), ALL(Products[Color]))
Revenue No Date Filter = CALCULATE(SUM(Sales[Amount]), REMOVEFILTERS('Date'))

ALLEXCEPT()

Removes filters from a table except the columns you list, the basis of percent-of-parent.

Revenue by Category = CALCULATE(SUM(Sales[Amount]), ALLEXCEPT(Products, Products[Category]))

KEEPFILTERS()

By default CALCULATE overrides; KEEPFILTERS() instead intersects the new filter with the existing one.

Blue Revenue = CALCULATE(SUM(Sales[Amount]), Products[Color] = "Blue")
// Overrides any color filter; always Blue.

Blue Revenue Kept = CALCULATE(SUM(Sales[Amount]), KEEPFILTERS(Products[Color] = "Blue"))
// Intersects: BLANK if the slicer is Red, Blue revenue if the slicer is Blue.

FILTER()

FILTER() builds a table by iterating rows; use it only when the condition references a measure, combines multiple columns, or needs a complex boolean.

Premium Revenue = CALCULATE(SUM(Sales[Amount]), FILTER(Products, Products[Price] > 100))
Profitable Revenue = CALCULATE(SUM(Sales[Amount]), FILTER(Sales, Sales[Amount] > Sales[Cost]))

Performance: Direct column filters use the storage engine and are far faster than FILTER(). Reach for FILTER() only when necessary.

Common CALCULATE Patterns

Percentage of total (clear all filters for the denominator):

% of Total Revenue =
VAR CurrentRevenue = SUM(Sales[Amount])
VAR TotalRevenue = CALCULATE(SUM(Sales[Amount]), ALL(Sales))
RETURN DIVIDE(CurrentRevenue, TotalRevenue)

Percentage of parent (keep the parent column with ALLEXCEPT):

% of Category =
VAR CurrentRevenue = SUM(Sales[Amount])
VAR CategoryRevenue = CALCULATE(SUM(Sales[Amount]), ALLEXCEPT(Products, Products[Category]))
RETURN DIVIDE(CurrentRevenue, CategoryRevenue)

Conditional aggregation:

Online Revenue = CALCULATE(SUM(Sales[Amount]), Sales[Channel] = "Online")

Context Transition

When CALCULATE wraps an expression that has a row context, it converts that row context into filter context, called context transition. This is how a measure behaves correctly inside an iterator like SUMX: each row's context becomes a filter so the measure evaluates for that single row. Context transition is also why placing a measure name inside SUMX or a calculated column produces row-specific values automatically; the engine wraps measures in an implicit CALCULATE.

On the Exam

The PL-300 tests how CALCULATE modifies filter context, using ALL() for percent-of-total, direct filters vs. FILTER(), KEEPFILTERS() vs. default override, and combining CALCULATE with time intelligence.

CALCULATE Is the Hinge of Advanced DAX

Almost every interesting calculation, period comparisons, percentages, segment filters, what-if logic, is built by reshaping filter context, and CALCULATE is the only direct way to do it. Internally, CALCULATE evaluates its filter arguments first to produce new filter sets, applies them on top of (or in place of) the existing context, performs context transition if it is running inside a row context, and only then evaluates the expression. Knowing this order, filters first, context transition, then expression, explains many otherwise surprising results and is exactly the level of reasoning the exam's harder DAX items expect.

Override vs. Intersect: the KEEPFILTERS Distinction

The default behavior, a CALCULATE filter on a column replaces any existing filter on that column, is the source of countless mistakes. If a slicer already restricts Color to Red and your measure does CALCULATE(SUM(Sales[Amount]), Products[Color] = "Blue"), the slicer is ignored and you get Blue. Wrapping the predicate in KEEPFILTERS changes replacement into intersection, so the same measure returns BLANK when the slicer is Red (no overlap) and Blue's value when the slicer is Blue. Use plain CALCULATE filters when you genuinely want to ignore the user's selection, and KEEPFILTERS when you want to respect it.

Boolean Filters Are Sugar for FILTER + ALL

A simple predicate like Products[Color] = "Red" is shorthand the engine expands to FILTER(ALL(Products[Color]), Products[Color] = "Red"). That hidden ALL is why a boolean filter replaces rather than intersects, and it is why boolean filters are fast (they operate on a single column at the storage engine). When you instead pass FILTER(Products, ...) over the whole table, you lose that optimization and iterate every row in the formula engine. This is the precise reason direct column predicates outperform table FILTERs, a point the optimization section builds on.

CALCULATE Inside Iterators and Measures

A point worth internalizing: a measure referenced inside an iterator like SUMX, or referenced from another measure, is wrapped by the engine in an implicit CALCULATE, which is what triggers context transition. This is why SUMX(Customers, [Total Revenue]) returns each customer's revenue and then sums them, the row context for each customer becomes a filter context that the measure honors. It also explains a subtle gotcha: a variable defined before a CALCULATE captures the outer context, so referencing that variable inside the CALCULATE does not pick up the new filters.

When a calculation gives an unexpected total, the first questions to ask are "what context is this expression in" and "did a CALCULATE override or intersect the existing filters," which is exactly the reasoning the harder exam items reward.