CALCULATE and Filter Context in DAX | Free Power BI Course: From Beginner to Advanced

What you'll learn

Use CALCULATE to add, replace or remove filters inside a measure.
Calculate percent of total with ALL, REMOVEFILTERS and ALLSELECTED.
Filter on conditions that involve measures with FILTER.
Tell row context from filter context and understand context transition.
Apply CALCULATE to Summit's real sales by category, city and customer type.

In the last lesson you saw that every cell in a visual has its own filter context. CALCULATE is the function that lets you change that context: calculate New York sales even when the cell belongs to Atlanta, the total for all products even when the row shows just one, or last year's sales (Lesson 8). It's the most important function in DAX; once it clicks, everything else falls into place.

39.5%of Summit's sales are dairy: a "percent of total" only CALCULATE can produce

17.0%comes from New York, with a measure that locks the city even when the cell shows another one

9 of 40customers bought more than $15,000 and account for 28.0% of sales, thanks to FILTER

The syntax

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

CALCULATE takes the current filter context, applies the filters you specify and, with that modified context, evaluates the expression (usually a measure).

Sales New York =
CALCULATE ( [Total Sales], Customers[City] = "New York" )

Sales Beverages New York =
CALCULATE (
    [Total Sales],
    Customers[City] = "New York",
    Products[Category] = "Beverages"
)

Several filters separated by commas combine with AND: beverage sales and in New York.

How it interacts with existing filters

Let's reuse the sample table from Lesson 6 (total sales of $324.60: Rice $162, Oil $117, Sugar $45.60). Create this measure and put it in a matrix by Product:

Sales Rice = CALCULATE ( [Total Sales], Products[Product] = "Rice" )

Product	Total Sales	Sales Rice
Rice	$162.00	$162.00
Oil	$117.00	$162.00
Sugar	$45.60	$162.00
Total	$324.60	$162.00

On the "Oil" row the context says Product = Oil, but CALCULATE replaces the filter on that same column with Product = Rice. The rule: a CALCULATE filter on a column overrides any existing filter on that column and leaves filters on other columns alone. If a slicer also filters City, Sales Rice shows rice sales for that city.

If you want the filter to intersect with the existing one instead of replacing it, wrap it in KEEPFILTERS: CALCULATE([Total Sales], KEEPFILTERS(Products[Product] = "Rice")). Then the Oil row would be blank, because a row can't be Oil and Rice at the same time.

1. A slicer is set to City = Chicago, and a card shows CALCULATE([Total Sales], Customers[City] = "New York"). What does the card display?

Chicago's sales.
New York's sales: the CALCULATE filter on City replaces the slicer's filter.
A blank.

A CALCULATE filter on a column overrides the existing filter on that same column.

Percent of total: ALL and REMOVEFILTERS

To see what share of sales each product represents, you divide the row's sales by the sales of all products. ALL (or REMOVEFILTERS, which does the same thing as a filter and reads more clearly) removes filters from a table or column:

Sales All Products =
CALCULATE ( [Total Sales], REMOVEFILTERS ( Products ) )

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

Product	Total Sales	Sales All Products	% of Total
Rice	$162.00	$324.60	49.9%
Oil	$117.00	$324.60	36.0%
Sugar	$45.60	$324.60	14.0%
Total	$324.60	$324.60	100.0%

Because it only removes filters from Products, if the user picks a city in a slicer, the percentage is calculated against that city's total. That's exactly what people expect.

ALLSELECTED: the total of what the user selected

If the user filters to show only Rice and Oil, should the percentage be calculated against $324.60 (everything) or $279 (what's visible)? ALLSELECTED(Products) removes the filters coming from the visual's rows but keeps the user's choices in slicers and filters. With it, Rice is 162 ÷ 279 = 58.1% and Oil 41.9%. Pick the one that answers your question.

2. You want each rep's percentage of the total for all reps, while respecting whatever year the user picks. Which denominator do you use?

CALCULATE([Total Sales], REMOVEFILTERS(SalesReps))
CALCULATE([Total Sales], REMOVEFILTERS()), which removes all filters.
SUM(Sales[Amount])

Removing only the SalesReps filters keeps the year filter. Removing every filter would ignore the selected year.

FILTER: conditions on measures

Simple CALCULATE filters compare a column to a value. When the condition depends on a measure ("customers who bought more than $100"), you need FILTER, which walks a table and keeps the rows that meet the condition:

Large Customers =
COUNTROWS (
    FILTER ( VALUES ( Customers[CustomerID] ), [Total Sales] > 100 )
)

Sales Large Customers =
CALCULATE (
    [Total Sales],
    FILTER ( VALUES ( Customers[CustomerID] ), [Total Sales] > 100 )
)

In the sample table, sales by customer are C1 = $144, C2 = $90.60 and C3 = $90. Only C1 tops $100, so Large Customers = 1 and Sales Large Customers = $144. With a $90 threshold, C2 would qualify ($90.60) but C3 wouldn't ($90 isn't greater than $90): 2 customers.

Filter columns, not whole tables

Run FILTER over the smallest table you can (such as VALUES(Customers[CustomerID]) or the dimension), not over a fact table with millions of rows. For simple column conditions, a direct filter (Customers[City] = "New York") is faster than FILTER.

The four modifiers at a glance

Simple filter

Customers[City] = "New York"

Effect: replaces any filter on that column and keeps the others.

KEEPFILTERS

KEEPFILTERS ( Customers[City] = "New York" )

Effect: intersects with the existing filter; if the cell is another city, the result is blank.

REMOVEFILTERS / ALL

REMOVEFILTERS ( Products )

Effect: clears filters from that table or column; the basis of percent of total.

ALLSELECTED

ALLSELECTED ( Products[Category] )

Effect: clears the visual's row filter but keeps the user's selections.

CALCULATE on Summit's data

Using the base measures from Lesson 6, create % of All Categories (it's in this lesson's exercise) and put it in a matrix by Category. For the full period you'll see:

% of all categories, 2025–2026

Dairy39.5%

Pantry30.8%

Beverages29.6%

(Blank)0.1%

Now select only Dairy and Beverages in a slicer. With REMOVEFILTERS the percentages don't change (39.5% and 29.6%) because the denominator is still the total of every category. With ALLSELECTED the denominator becomes the selection, $356,516.45, and the percentages add up to 100%: Dairy 57.1% and Beverages 42.9%. Neither is "wrong": they answer different questions.

Other calculations you'll be asked for all the time, with their Summit results:

Sales Convenience and Grocers =
CALCULATE (
    [Total Sales],
    Customers[Channel] IN { "Convenience Store", "Independent Grocer" }
)
-- $311,382.80: 60.3% of sales

Sales Dairy 2026 =
CALCULATE (
    [Total Sales],
    Products[Category] = "Dairy",
    'Calendar'[Year] = 2026
)
-- $107,276.90 (requires the Calendar table from Lesson 8)

The IN operator with a list in curly braces is shorthand for several ORs on the same column. And notice that filters on different columns (category and year) combine with AND.

Row context and context transition

DAX has two kinds of context:

Filter contextWhich rows are visible

What it is: The filters applied to tables.
Where it comes from: The visual's rows and columns, slicers, page and report filters, and CALCULATE.
Effect: Shrinks the rows that SUM, COUNTROWS and every measure can see.

Filters

Row contextThe current row

What it is: "The current row" while a table is being iterated.
Where it comes from: A calculated column or an iterator such as SUMX or FILTER.
Effect: Lets you read Sales[Quantity] on that row, but doesn't filter anything by itself.

Reads a row

When you use a measure inside an iterator (like [Total Sales] inside FILTER above), DAX automatically wraps it in CALCULATE and turns the current row into a filter. That's called context transition, and it's what lets [Total Sales] calculate sales for each customer as FILTER walks through them. If you wrote SUM(Sales[Amount]) without CALCULATE instead, you'd get the grand total on every row.

3. Inside FILTER(VALUES(Customers[CustomerID]), [Total Sales] > 100), why does [Total Sales] return each customer's sales instead of the grand total?

Because FILTER always sums by customer.
Because VALUES deletes the other customers from the table.
Context transition: the measure gets an implicit CALCULATE and the current row becomes a filter.

Measures used inside an iterator turn row context into filter context.

4. At Summit, a user selects Dairy and Beverages in a slicer. What percentage does Dairy show with DIVIDE([Total Sales], CALCULATE([Total Sales], ALLSELECTED(Products[Category])))?

39.5%
57.1%
100%

ALLSELECTED uses the selection as the denominator: $203,741.64 ÷ $356,516.45 = 57.1%. With REMOVEFILTERS the denominator would be every category, giving 39.5%.

Common CALCULATE mistakes

Expecting CALCULATE([Sales], City = "New York") to respect the row's city.

A filter on the same column replaces it; use KEEPFILTERS to intersect.

Using REMOVEFILTERS() with no arguments for a percent of total.

Remove only the filters of the dimension being split (Products, SalesReps…).

FILTER over the entire Sales table for a simple condition.

A direct column filter; FILTER over VALUES of a dimension when the condition is a measure.

Using SUM inside FILTER and expecting each customer's value.

Use a measure (or CALCULATE) to trigger context transition.

Mixing REMOVEFILTERS and ALLSELECTED without knowing which question you're answering.

Write the question down: "% of the company total, or % of what the user selected?"

Practice exercise: measures with CALCULATE

Create "Sales New York" and "% Sales New York" (Sales New York ÷ Total Sales).
Create "% of All Categories" and put it in a matrix by Category and Region.
Create "% of Selected" with ALLSELECTED and compare it with the previous one after filtering two categories.
Create "Customers Over 1000" (how many customers bought more than $1,000) and "Sales Customers Over 1000" (how much they bought).

See solution

Sales New York =
CALCULATE ( [Total Sales], Customers[City] = "New York" )

% Sales New York =
DIVIDE ( [Sales New York], [Total Sales] )

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

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

Customers Over 1000 =
COUNTROWS ( FILTER ( VALUES ( Customers[CustomerID] ), [Total Sales] > 1000 ) )

Sales Customers Over 1000 =
CALCULATE (
    [Total Sales],
    FILTER ( VALUES ( Customers[CustomerID] ), [Total Sales] > 1000 )
)

% Sales New York makes sense in a card or by category, but not in a table by city: on the Atlanta row, the CALCULATE filter replaces the city, so you'd divide New York's sales by Atlanta's, a meaningless number.

Summit results for the full period: Sales New York $87,472.73 and % Sales New York 17.0%. "Customers Over 1000" returns 40, because every customer clears that bar (the smallest bought $7,260.65). Change the threshold to 15,000 and 9 customers remain, with $144,657.51, or 28.0% of sales.

Summary

CALCULATE evaluates an expression in a modified filter context.
A filter on a column replaces the existing filter on that column; KEEPFILTERS intersects instead.
REMOVEFILTERS or ALL clear filters for percent of total; ALLSELECTED respects the user's selection.
FILTER handles conditions on measures; run it over small tables.
Measures inside iterators trigger context transition: the current row becomes a filter.
REMOVEFILTERS answers "% of total"; ALLSELECTED, "% of selection". At Summit, dairy is 39.5% or 57.1% depending on the question.

Next lesson: Date Tables and Time Intelligence: YTD, Prior Year and Growth. You'll build a date table and calculate year-to-date, prior-year comparisons and growth rates.