Skip to content
Tutorials Center
My learning

Topics

Lesson 24 of 56 ยท DAX

CALCULATE and Filter Context

Learn filter context, how CALCULATE changes it, ALL, REMOVEFILTERS, KEEPFILTERS, context transition and variables, with a runnable Python stand-in for the engine.

If DAX has one function you must understand, it is CALCULATE. It is the way to say "give me this number, but with different filters." Percent of total, sales for one category, last year's value and almost every other useful measure are built on it. To use it well you need one idea first: filter context.

This lesson explains filter context, shows how CALCULATE rewrites it, covers ALL, REMOVEFILTERS and KEEPFILTERS, then explains context transition and variables. The functions you call inside are the ones from Aggregators and Iterators.

DAX and its engine cannot run on this site, so the DAX here is read-only. To make the ideas tangible, the examples use a tiny Python stand-in for the engine: a table, a filter context stored as a dictionary, and helper functions named after the DAX ones. It illustrates the rules; it is not how Power BI is implemented, and it ignores relationships and many details.

Filter context

A measure never calculates "over the whole table". It calculates over the rows that survive the filters currently in force. Where do those filters come from?

  • The cell of a visual: a bar for North adds the filter Region = North.
  • Slicers and the filter pane.
  • Other fields on the axis, legend, rows and columns of a matrix.
  • Filters flowing along relationships from dimension tables to fact tables.

Together these are the filter context of that cell. Look at a matrix with Region in rows and Year in columns, showing Total Sales = SUM(Sales[Amount]). The cell for North, 2025 is evaluated with the filter context Region = North and Year = 2025. The same formula, a different context, a different answer.

One measure, many contexts

Cell filters Row and column headers.
Slicers What the reader picked.
Relationships Dimensions filter facts.

All of them are combined into one filter context per cell.

1 / 2

CALCULATE: change the context

DAX
CALCULATE(<expression>, <filter1>, <filter2>, ...)

CALCULATE takes the current filter context, modifies it with the filters you list, and then evaluates the expression in that new context. A simple filter argument looks like this:

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

The expression Product[Category] = "Bikes" is shorthand for "all category values that equal Bikes". Because it filters the Category column, it replaces any existing filter on that column, but leaves filters on other columns alone. So Bike Sales in the North row shows North bike sales, while in a Gear row it still shows bike sales, because the Gear filter on Category was overwritten.

Two limits to know: a filter argument can reference only one column in this shorthand, and it cannot use measures. For more complex conditions, pass a table from FILTER(...).

Removing filters: ALL and REMOVEFILTERS

To calculate "ignoring" a filter, remove it. ALL returns all values of a column or table, discarding filters on it; REMOVEFILTERS does the same but is only allowed inside CALCULATE and reads more clearly.

DAX
Sales All Regions = CALCULATE([Total Sales], REMOVEFILTERS(Sales[Region]))
Sales Grand Total = CALCULATE([Total Sales], REMOVEFILTERS())
Share of Total    = DIVIDE([Total Sales], [Sales Grand Total])

REMOVEFILTERS() with no arguments clears every filter from all tables. With a column it clears just that column; with a table it clears the whole table. The Share of Total measure divides the cell's value by the unfiltered one, which is the classic percent of total pattern. We refine it with ALLSELECTED in Ranking, Top N and Percent of Total.

KEEPFILTERS: respect what is already there

By default a column filter in CALCULATE overrides an existing filter on the same column. KEEPFILTERS changes that: the new filter is intersected with the existing one.

DAX
Bikes Within Selection =
CALCULATE([Total Sales], KEEPFILTERS(Product[Category] = "Bikes"))

If a slicer has selected Gear, then CALCULATE without KEEPFILTERS would still return bike sales (it overwrote the slicer), while with KEEPFILTERS the result is blank, since no row is both Gear and Bikes. Use KEEPFILTERS when your calculation should stay inside the user's selection.

Context transition

Row context and filter context are different, but CALCULATE can convert one into the other. When CALCULATE runs inside a row context, it turns the current row's column values into filters. That is context transition.

It happens in two common places. First, whenever you reference a measure inside an iterator or a calculated column, because a measure reference is silently wrapped in CALCULATE:

DAX
Sales per Region Row =
SUMX(VALUES(Sales[Region]), [Total Sales])

Inside SUMX each region is the current row, and [Total Sales] (which equals CALCULATE(SUM(...))) receives the filter Region = <that row>. In a calculated column on a Customer table, [Total Sales] gives each customer's own sales for the same reason. Without the measure, a bare SUM(Sales[Amount]) in a row context would ignore the row and return the grand total.

Context transition in 3 steps

Row context An iterator visits one row.
CALCULATE Turns that row into filters.
Filter context The expression sees only that row.

A measure reference is an implicit CALCULATE.

1 / 2

A Python stand-in for the engine

The code below models a filter context as a dictionary (column -> set of allowed values). Helper functions imitate CALCULATE, a plain column filter, ALL and KEEPFILTERS. It is deliberately tiny, but running it shows each rule above with real numbers.

Python Filter context and CALCULATE

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Read the three numbers in each row. total follows the row's filter. bikes adds a category filter on top of the region filter, since it touches a different column. all-regions removed the region filter (so it is always 670), and dividing by it gives each region's share of the total, which adds up to 100%.

Override vs KEEPFILTERS, and context transition, run live

The second example uses the same table and tests the two subtle behaviors.

Python KEEPFILTERS and context transition

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Without the transition, every region reports 670 (the grand total), which is the classic bug: a bare aggregation inside a row context ignores the row. With the transition each region sees only its own rows (North 290, South 230, East 150). The stand-in simply toggles whether the row becomes a filter; real DAX does that automatically when a measure (or CALCULATE) is evaluated inside a row context.

Variables make it readable

VAR stores a value computed in the current context at the point of definition. Later changes to the filter context do not alter it, which is exactly why variables are useful in CALCULATE:

DAX
Share of Region Total =
VAR CellValue   = [Total Sales]
VAR RegionTotal = CALCULATE([Total Sales], REMOVEFILTERS(Sales[Category]))
RETURN
    DIVIDE(CellValue, RegionTotal)

Each variable is evaluated once, before RETURN, in the context where it is defined. This both speeds up the formula and makes the logic easy to follow. Use clear names, and put the value that anchors your calculation first.

Common mistakes

  • Forgetting that a column filter replaces an existing one on the same column, and then being surprised that a slicer seems ignored.
  • Using ALL(Table) when you meant ALL(Table[Column]), which removes many more filters than you wanted.
  • Expecting CALCULATE to filter with a measure inside the shorthand filter; use FILTER for that.
  • Placing a bare aggregation inside an iterator and forgetting the missing context transition. Wrap it in CALCULATE, or reference a measure.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. In a matrix, the cell for North, 2025 shows Total Sales. What is its filter context?

    Each cell's filter context comes from its row and column headers (plus slicers and relationships), so this cell sees only North rows of 2025.
  2. 2. What does CALCULATE([Total Sales], Product[Category] = "Bikes") do when a slicer already selects Gear?

    A filter on a column in CALCULATE overrides the existing filter on that same column. Use KEEPFILTERS if you want an intersection instead.
  3. 3. Which function removes the filters on the Region column inside CALCULATE?

    REMOVEFILTERS (or ALL on a column) clears the filter, which gives the unfiltered total used as the denominator of a percent of total.
  4. 4. What is context transition?

    When CALCULATE (or a measure reference, which is an implicit CALCULATE) runs in a row context, the row's column values become filters.
  5. 5. When is a VAR in a measure evaluated?

    A variable stores a value computed once at its definition, so later CALCULATE changes do not alter it.

Summary

  • Filter context is the set of filters (cell headers, slicers, relationships) in force when a measure is evaluated.
  • CALCULATE evaluates an expression in a modified filter context; a column filter replaces an existing filter on that column.
  • REMOVEFILTERS and ALL clear filters; KEEPFILTERS intersects instead of replacing.
  • Context transition turns a row context into filters; a measure reference is an implicit CALCULATE.
  • Variables are evaluated once, where they are defined, which keeps formulas fast and clear.

Next, we put CALCULATE to work on dates: Time Intelligence.