DAX CALCULATE Explained With Examples: Filter Context Made Simple

CALCULATE evaluates an expression, usually a measure, after changing the filter context it runs in. You give it the expression first and then one or more filters, for example CALCULATE([Total Sales], Product[Category] = "Bikes"). Each filter either replaces the existing filter on that column or adds a new one, and modifiers such as REMOVEFILTERS, ALL and KEEPFILTERS control exactly how. Almost every useful DAX pattern in Power BI, from percentage of total to year-to-date, is CALCULATE underneath.

I'm Gulab Chand Tejwani, a Microsoft Certified Trainer and PL-300 certified Power BI analyst. When I teach DAX in Anand, CALCULATE is the day the room goes quiet. Not because the syntax is hard, but because it forces you to think about filter context, which most Excel users have never had to name. This post is the explanation I wish I'd had.

What is filter context in Power BI?

Filter context is the set of filters active when a measure is calculated. It comes from everywhere on the report: the row and column headers of a matrix, slicers, the filter pane, cross-filtering from another visual, and relationships that carry those filters from one table to another.

Take a simple measure:

Total Sales = SUM ( Sales[Amount] )

Put it in a matrix with Category on rows and it shows a different number on each row. The formula didn't change. The filter context did. On the "Bikes" row, the filter Product[Category] = "Bikes" travels through the relationship to the Sales table, and SUM only sees bike sales.

That's the idea to hold onto: a measure has no fixed value. It has a value for each filter context. CALCULATE is how you change that context on purpose.

What is the syntax of CALCULATE?

CALCULATE ( <expression> [, <filter1> [, <filter2> [, …]]] )

The expression is what you want to compute. The filters are boolean conditions like Product[Color] = "Red", table expressions, or filter modifier functions. Microsoft's CALCULATE reference on Microsoft Learn lists the full rules. Three of them matter most day to day:

1. A boolean filter on a column replaces any existing filter on that same column. It does not intersect with it.
2. Multiple filter arguments are combined with AND.
3. CALCULATE also performs context transition: if it runs inside a row context (in a calculated column or an iterator like SUMX), the current row becomes a filter.

In what order does CALCULATE do its work?

When a CALCULATE gives a strange number, it nearly always helps to walk through what happens inside it, in order. This is the simplified sequence I draw on the whiteboard.

Two consequences matter in practice. First, filter arguments are evaluated in the original context, before anything changes, so a filter like 'Date'[Date] = MAX('Date'[Date]) sees the dates from the visual. Second, because modifiers such as REMOVEFILTERS run before your new filters are applied, CALCULATE([Total Sales], REMOVEFILTERS(Product), Product[Color] = "Red") correctly returns red sales across all categories.

Nine CALCULATE examples you can use today

All examples assume a simple model: a Sales fact table with Amount, Quantity, OrderDate, ShipDate and ProductKey, related to a Product table and a Date table.

1. Sales for one category

Bike Sales =
CALCULATE ( [Total Sales], Product[Category] = "Bikes" )

Put this in a matrix by Category and you'll see the same bike figure on every row, including the "Accessories" row. Why? Because the filter Category = "Bikes" replaced the row's own Category filter (rule 1). That's not a bug. It's exactly what makes the next examples work.

2. Keep the existing filter with KEEPFILTERS

If you want "bike sales, but only where the row already allows bikes", wrap the filter:

Bike Sales (Respect Rows) =
CALCULATE ( [Total Sales], KEEPFILTERS ( Product[Category] = "Bikes" ) )

Now the Bikes row shows bike sales and every other row is blank. KEEPFILTERS makes the new filter intersect with the existing one instead of overwriting it.

3. Percentage of total with REMOVEFILTERS

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

The denominator removes only the Category filter, so it returns total sales for all categories while still respecting any year or region slicer. I use REMOVEFILTERS rather than ALL here because the name says what it does. They behave the same when used as a CALCULATE modifier.

4. Two conditions at once

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

Separate filter arguments are combined with AND. If you need OR across the same column, use Product[Color] IN { "Red", "Black" }. For OR across different columns, you need a table filter with FILTER, which brings us to the next example.

5. A filter based on a measure

You can't write CALCULATE([Total Sales], [Margin %] > 0.3). A boolean filter argument must refer to columns, not measures. Use FILTER over a table instead:

Sales of High-Margin Products =
CALCULATE (
    [Total Sales],
    FILTER ( VALUES ( Product[ProductName] ), [Margin %] > 0.3 )
)

FILTER walks through each product name in the current context, calculates the margin for each one, and keeps those above 30%. Keep the table small (VALUES of one column, not the whole Product table) or this gets slow on big models.

6. Sales last year

Sales LY =
CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )

SAMEPERIODLASTYEAR returns a table of dates shifted back one year, and CALCULATE uses it as the filter. This only behaves properly with a complete, marked date table, which I cover in a separate post on DAX time intelligence.

7. Context transition in a calculated column

In the Product table, add a calculated column:

Product Total Sales = CALCULATE ( SUM ( Sales[Amount] ) )

There's no filter argument at all, yet each product gets its own total. That's context transition: CALCULATE turned the current product row into a filter. Without CALCULATE, SUM(Sales[Amount]) in a calculated column gives the grand total on every row. Measures referenced inside an iterator get this behaviour automatically, because every measure reference has an invisible CALCULATE around it.

8. Sales by ship date with USERELATIONSHIP

If Sales has both OrderDate and ShipDate related to the Date table, only one relationship can be active. Say OrderDate is active. To report by ship date without changing the model:

Sales by Ship Date =
CALCULATE ( [Total Sales], USERELATIONSHIP ( Sales[ShipDate], 'Date'[Date] ) )

USERELATIONSHIP activates the inactive relationship just for this calculation. This exact pattern turns up in PL-300 scenario questions about role-playing dimensions.

9. Share of the visible total with ALLSELECTED

Percentage of total (example 3) ignores the Category filter completely. Sometimes the business wants the share of what's currently selected in a slicer instead:

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

If the slicer shows only Bikes and Accessories, the shares add to 100% across those two. Use it carefully: ALLSELECTED depends on what the user has selected outside the visual, which is useful in a report and confusing when you reuse the measure elsewhere.

Which CALCULATE modifier should you use?

These are the functions I see most often inside CALCULATE, and the one question each answers.

Function	What it does	Typical use
KEEPFILTERS	Intersects the new filter with the existing one	Restrict a measure without overriding rows
REMOVEFILTERS	Clears filters from a table or columns	Denominators for percentage of total
ALL	Clears filters; can also return a table	Iterating every row regardless of filters
ALLEXCEPT	Clears all filters on a table except listed columns	Share within a group, such as within a region
ALLSELECTED	Keeps filters from outside the visual only	Share of what the user has selected
USERELATIONSHIP	Activates an inactive relationship	Ship date vs order date reporting

What mistakes do people make with CALCULATE?

The same five come up in almost every batch I teach:

• Expecting filters to intersect. They replace by default. If the numbers look "stuck" on every row, you probably need KEEPFILTERS or a different design.
• Using ALL on the whole table. ALL(Sales) removes every filter coming from the fact table, often more than you meant. Remove filters on the specific columns or dimension table instead.
• Filtering the fact table instead of the dimension. Filter Product[Category], not Sales[Category] (if you've copied category into Sales). Filters on dimensions flow through relationships the way the star schema intends.
• Wrapping big tables in FILTER. FILTER(Sales, …) over millions of rows is slow. Use a boolean column filter wherever possible.
• Debugging blind. Build the measure step by step in a table visual, or use DAX query view in Power BI Desktop to run the expression and see the result before it goes into a chart.

A worked debugging example

Here's a case I set in class. A student writes a measure for "this year's sales" and puts it in a matrix with 'Date'[Year] on rows. Every row, 2024, 2025 and 2026, shows the same number.

Sales This Year =
CALCULATE ( [Total Sales], 'Date'[Year] = YEAR ( TODAY () ) )

Walk through the steps from the figure above. The row brings in a filter on 'Date'[Year], say 2024. Then step 4 applies the measure's own filter on the same column, 'Date'[Year] = 2026, and it replaces the row's filter. So every row calculates 2026 sales.

There are two fixes, depending on what the business wants. If the measure should be blank on rows other than the current year, wrap the filter in KEEPFILTERS. If they actually wanted each row's own sales, they didn't need CALCULATE at all: [Total Sales] already responds to the row.

The lesson: when a CALCULATE result looks wrong, write down the filter context coming in (the visual, the relationships, the slicers) and then apply the steps in order. The bug is usually visible within a minute.

How does CALCULATE appear in PL-300?

"Use the CALCULATE function" is a named line in the PL-300 skills outline, under creating model calculations with DAX. Time intelligence and semi-additive measures, both listed alongside it, also depend on CALCULATE. Expect scenario questions that show a measure and ask why a total is wrong, or ask which modifier gives the required result. My PL-300 exam guide walks through the full outline and a six-week study plan.

How to practise CALCULATE properly

Reading about filter context only gets you so far. You need a model, a matrix and a question you can't answer yet. Here's the exercise I give in class: build a matrix of Category by Year, then write measures for sales, sales for bikes only, share of category, share of year, and growth versus last year. When all five numbers make sense on every cell, including the totals, you understand CALCULATE.

If you'd like someone to check your measures as you go, our Power BI training in Anand and live online spends 10 of its 30 teaching hours on data modelling and DAX, with time intelligence and CALCULATE practised on real datasets. Prefer to work alone? The self-paced Power BI online course includes DAX lessons and hands-on labs you can do at your own speed. And if you're still wondering whether DAX is worth the effort, read why DAX is important in data analysis.

FAQ

What does CALCULATE do in DAX?

It evaluates an expression in a modified filter context. You pass the expression first and then filters that add to, replace or remove the filters already applied by the report.

What is the difference between CALCULATE and FILTER?

CALCULATE changes the filter context and evaluates an expression. FILTER is a table function that returns the rows of a table meeting a condition. You often use FILTER inside CALCULATE when the condition involves a measure.

When should I use ALL versus REMOVEFILTERS?

Inside CALCULATE they do the same job of removing filters. Use REMOVEFILTERS when your intent is only to clear filters, because the name is clearer. Use ALL when you need it as a table function, for example to iterate over every row regardless of filters.

Why does my CALCULATE measure show the same value on every row?

Your filter argument is replacing the filter that each row applies on the same column. Wrap the filter in KEEPFILTERS if you want it to intersect with the row's own filter. Also check that the visual uses columns from the dimension tables, not the fact table.

Is CALCULATE on the PL-300 exam?

Yes. "Use the CALCULATE function" is listed in the PL-300 skills outline under creating model calculations by using DAX.