DAX CALCULATE and Filter Context Explained for Beginners

CALCULATE is the DAX function that evaluates an expression, usually a sum or another measure, after changing the filters it runs under. Those filters are called the filter context: the row and column of the visual, slicers, page filters and relationships. Once you understand that CALCULATE adds, replaces or removes filters before calculating, most “why is my number wrong?” moments in Power BI make sense.

This guide gives you DAX CALCULATE explained in plain English, with a small sales model, six measures you can type in today and the mistakes beginners make. It is the same order we teach DAX in our Advanced Power BI course.

Quick answer: Every measure is calculated inside a filter context set by the visual, slicers and relationships. CALCULATE(<expression>, <filters>) changes that context first: a filter on a column already filtered replaces it, a new filter is added, and modifiers like REMOVEFILTERS, ALL and KEEPFILTERS remove or combine filters. Called inside a row context, CALCULATE also turns the current row into a filter (context transition).

The model we will use

A simple star schema, the shape most Power BI models should have:

• Sales (fact table): OrderDate, ProductKey, StoreKey, Quantity, Amount
• Product: ProductKey, Product, Category, Color
• Store: StoreKey, Store, Region
• Date: Date, Month, Year (marked as a date table)

Each dimension has a one-to-many relationship to Sales. Our base measure is:

Total Sales = SUM ( Sales[Amount] )

What filter context is, before CALCULATE

Put Total Sales in a matrix with Region on rows. The East row shows East’s sales, West shows West’s. You wrote one measure, but it returns a different number in each cell. Why?

Because each cell is calculated with its own filter context. For the East cell, Power BI filters Store[Region] = “East”; that filter flows through the relationship to Sales, and SUM only sees East’s rows. The total row has no Region filter, so it sees everything.

Filter context comes from:

1. Rows and columns of the visual
2. Slicers and cross-filtering from other visuals
3. Visual, page and report filters
4. Relationships that carry those filters from dimensions to facts
5. Row-level security, if you use it

If you have built Excel pivot tables, this is exactly what a pivot does when it places a value in a cell. Our pivot table tutorial is a good warm-up if the idea feels new.

How CALCULATE changes filter context

The syntax from Microsoft’s DAX reference is:

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

CALCULATE takes the current filter context, applies your filter arguments, then evaluates the expression. The rules a beginner needs:

Situation	What CALCULATE does
Your filter is on a column that is not filtered yet	Adds it
Your filter is on a column that is already filtered	Replaces the existing filter on that column
You wrap the filter in KEEPFILTERS	Combines it with the existing filter instead
You use REMOVEFILTERS or ALL	Removes filters from those columns or tables
Several filter arguments	All apply together (AND)

Six CALCULATE measures beginners use every day

1. Sales for one category

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

On a Region row, this shows Electronics sales for that region: the Region filter stays, and the Category filter is added.

2. Overwrite versus keep: the colour example

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

Put Color on rows. On the Red row, Blue Sales shows the Blue total, not blank. That surprises everyone the first time: the Red filter on Color was replaced by Blue. If you want Red rows to show blank, keep both filters:

Blue Sales (Keep) =
CALCULATE ( [Total Sales], KEEPFILTERS ( Product[Color] = "Blue" ) )

3. Percentage of all regions

Sales % of All Regions =
DIVIDE (
    [Total Sales],
    CALCULATE ( [Total Sales], REMOVEFILTERS ( Store[Region] ) )
)

The denominator removes only the Region filter, so a Category slicer still works. Use DIVIDE rather than / so a zero total returns blank instead of an error.

4. Two conditions on one column

A simple filter argument can reference only one table’s columns and cannot use measures. For an OR on one column, write it inside the condition:

Big Two Regions =
CALCULATE ( [Total Sales], Store[Region] IN { "East", "West" } )

5. A condition that needs a measure: FILTER

When the condition depends on a calculation, use FILTER over the smallest table that works:

Sales from Big Stores =
CALCULATE (
    [Total Sales],
    FILTER ( VALUES ( Store[Store] ), [Total Sales] > 100000 )
)

Avoid FILTER ( Sales, … ) over the whole fact table when a column filter will do; it is slower and changes the meaning.

6. Time intelligence: last year’s sales

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

Time intelligence functions are just filter arguments that return a set of dates. They need a proper, continuous date table marked as a date table.

Context transition: CALCULATE inside a row context

Row context exists when DAX goes row by row: in a calculated column, or inside iterators like SUMX. A row context does not filter other tables on its own. CALCULATE fixes that by turning the current row into a filter context. This is called context transition.

In a calculated column on the Product table:

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

Without CALCULATE, every product row would show the grand total. With it, each row shows its own sales. When you reference a measure inside a row context, Power BI wraps it in CALCULATE for you, which is why SUMX ( Product, [Total Sales] ) works as expected.

Common CALCULATE mistakes

• Expecting a filter to “add on” when it replaces. Remember the Blue-on-Red-row example, and reach for KEEPFILTERS when you mean “and also”.
• Using ALL on the whole table when you meant one column. REMOVEFILTERS ( Store ) clears Region and Store; REMOVEFILTERS ( Store[Region] ) clears only Region.
• Filtering the fact table instead of the dimension. Filter Product[Category], not a category text copied into Sales.
• Calculated columns where a measure belongs. Columns are fixed at refresh and ignore slicers. Totals that should react to the report belong in measures.
• No date table. Time intelligence returns odd results with gaps in dates or a date column from the fact table.

How to practise CALCULATE

1. Build the four-table model above with any sample sales data.
2. Create the six measures and put each in a matrix next to Total Sales.
3. Before adding each measure to the visual, predict the number in two or three cells. Then check.
4. Use the Performance analyzer or DAX query view to inspect what a visual runs.
5. Rewrite one measure using variables (VAR … RETURN) so it is easier to read.

Predicting before checking is what turns memorised syntax into understanding. Our Power BI roadmap shows where DAX sits in the wider learning path, from Power Query to publishing.

Learn it with Omebly

Omebly Academy’s Advanced Power BI course covers star schemas and DAX essentials, including CALCULATE, filter context and time intelligence, in month 2. Classes are live and mentor-led, with recordings and weekly one-to-one reviews, and the syllabus maps to the PL-300 exam skills (the exam itself is taken directly with Microsoft).

• Core (3 months, $699): Power BI Desktop and Power Query, modelling and DAX essentials, report design, publishing and row-level security, with two capstone projects.
• Professional (6 months, $1,199): adds advanced DAX patterns, performance tuning, dataflows, SQL sources and deployment pipelines, four capstones, mock interviews and portfolio review.
• Career (12 months, $1,999): adds a Fabric and lakehouse introduction, Python visuals, governance and a live client-style BI project, with unlimited mentor reviews and placement assistance.

You receive an Omebly certificate of completion with your projects listed. Placement assistance covers CV and LinkedIn help, mock interviews, portfolio review and referrals where possible; there is no job guarantee. If Excel still feels shaky, start with the Advanced Excel course.

Stuck on a measure or unsure which track fits? Contact us or message us on WhatsApp to book a free first call.