The CALCULATE Function

Quick Answer: CALCULATE modifies filter context before evaluating an expression. It is the most powerful and most tested DAX function. CALCULATE(SUM(Sales[Amount]), Products[Color] = "Red") calculates the sum of Amount BUT only for Red products, regardless of what color is selected in the current filters. ALL() removes filters, KEEPFILTERS() preserves them.

Why CALCULATE is Critical

CALCULATE is the single most important function in DAX because it is the only way to modify filter context within a measure. Nearly all advanced DAX patterns use CALCULATE.

Syntax

CALCULATE(
    <expression>,          -- What to calculate
    <filter1>,             -- How to modify filters (optional)
    <filter2>,             -- Additional filter modifications (optional)
    ...
)

How CALCULATE Works

1. Takes the current filter context (from slicers, visuals, report filters)
2. Modifies the context using the specified filters
3. Evaluates the expression in the modified context

Simple Examples

// Revenue for Red products only
Red Revenue =
CALCULATE(
    SUM(Sales[Amount]),
    Products[Color] = "Red"
)

// Revenue for 2026 only
Revenue 2026 =
CALCULATE(
    SUM(Sales[Amount]),
    'Date'[Year] = 2026
)

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

Multiple filters in CALCULATE are combined with AND logic.

Filter Modifiers

ALL() — Remove All Filters

ALL() removes filters from a table or specific columns:

// Total revenue ignoring ALL filters
Total Revenue All =
CALCULATE(
    SUM(Sales[Amount]),
    ALL(Sales)
)

// Revenue ignoring only the Color filter
Revenue All Colors =
CALCULATE(
    SUM(Sales[Amount]),
    ALL(Products[Color])
)

ALLEXCEPT() — Remove All Filters Except Specified

// Remove all filters on Products EXCEPT Category
Revenue by Category =
CALCULATE(
    SUM(Sales[Amount]),
    ALLEXCEPT(Products, Products[Category])
)

REMOVEFILTERS() — Explicit Filter Removal

REMOVEFILTERS() is an alias for ALL() when used inside CALCULATE, but the name makes the intent clearer:

Revenue No Date Filter =
CALCULATE(
    SUM(Sales[Amount]),
    REMOVEFILTERS('Date')
)

KEEPFILTERS() — Preserve Existing Filters

By default, CALCULATE filters override existing filters on the same column. KEEPFILTERS() changes this to intersect with existing filters:

// WITHOUT KEEPFILTERS (default):
// If user selects "Red" in a slicer, this OVERRIDES to "Blue"
Blue Revenue =
CALCULATE(SUM(Sales[Amount]), Products[Color] = "Blue")
// Result: Blue revenue regardless of slicer selection

// WITH KEEPFILTERS:
// If user selects "Red" in a slicer, this INTERSECTS with "Blue"
Blue Revenue Kept =
CALCULATE(SUM(Sales[Amount]), KEEPFILTERS(Products[Color] = "Blue"))
// Result: BLANK if slicer = "Red" (no intersection), Blue revenue if slicer = "Blue"

FILTER() — Row-by-Row Filtering

FILTER() creates a table filter by iterating over each row:

// Revenue for products priced above $100
Premium Revenue =
CALCULATE(
    SUM(Sales[Amount]),
    FILTER(Products, Products[Price] > 100)
)

// Revenue for products where revenue exceeds cost
Profitable Revenue =
CALCULATE(
    SUM(Sales[Amount]),
    FILTER(Sales, Sales[Amount] > Sales[Cost])
)

When to use FILTER():

• When the filter condition references a measure (not just a column)
• When the condition involves multiple columns in an AND/OR pattern
• When you need a complex boolean expression

Performance Note: Direct column filters (Products[Color] = "Red") are faster than FILTER() because they use the storage engine. Use FILTER() only when necessary.

Common CALCULATE Patterns

Pattern 1: Percentage of Total

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

Pattern 2: Percentage of Parent

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

Pattern 3: Year-Over-Year Comparison

YoY Revenue Change =
VAR CurrentYear = SUM(Sales[Amount])
VAR PriorYear =
    CALCULATE(
        SUM(Sales[Amount]),
        SAMEPERIODLASTYEAR('Date'[Date])
    )
RETURN
    CurrentYear - PriorYear

Pattern 4: Conditional Aggregation

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

CALCULATE Context Transition

When CALCULATE wraps a row context expression, it converts the row context into an equivalent filter context. This is called context transition.

// In a calculated column, [Amount] has row context
// Wrapping in CALCULATE converts row context to filter context
Running Total =
CALCULATE(SUM(Sales[Amount]))

Advanced: Context transition is how measures work inside iterator functions like SUMX. Each iteration's row context is converted to filter context for the measure evaluation.

On the Exam

The PL-300 frequently tests:

• Understanding how CALCULATE modifies filter context
• Using ALL() to calculate percentage of total
• Difference between CALCULATE with direct filters vs. FILTER()
• KEEPFILTERS() vs. default filter override behavior
• Combining CALCULATE with time intelligence functions