Downloads

CALCULATE and filter context

The most important function in DAX: add, remove and keep filters.

Last updated

View as Markdown For AI agents All guides as text

On this page

You'll be able to

  • Use CALCULATE to add, remove and keep filters inside a measure.
  • Write boolean and table filter arguments, and know when FILTER is needed.
  • Read the current filter state with ALL, ALLEXCEPT, VALUES, SELECTEDVALUE and related functions.
  • Avoid the common CALCULATE mistakes that give a wrong total.

CALCULATE is the most important function in DAX: CALCULATE(<expression>, <filter1>, <filter2>, ...) evaluates the expression in a filter context that you modify. Almost every non-trivial measure is CALCULATE in some form.

How CALCULATE works

  1. The incoming filter context (slicers, rows, columns) is copied.
  2. Any row context is converted to filters (context transition).
  3. Each filter argument is applied: it either replaces, removes or adds to the filters on the columns it names.
  4. The expression is evaluated in the new context. Filter arguments are evaluated in the ORIGINAL context and then applied together as an AND, never one after another.
Incoming slicer: Store CALCULATE filter argument Evaluated Total Sales Store Online Catalog Reseller Store Online Catalog Reseller Store Online Catalog Reseller Only Online is summed replaces Store 1 copies the incoming filters. 2 applies the CALCULATE filter. 3 evaluates the measure with the new filter in place.
CALCULATE changes the filter context. A new filter on the same column replaces the old one.

Boolean filter arguments

CALCULATE([Total Sales], Channel[Channel] = "Online") is shorthand for filtering ALL values of the column that meet the condition. A Boolean filter REPLACES any existing filter on that same column, so on a table of channels every row shows the Online figure. Boolean filters can name only one column and cannot call a measure. Use IN for a list: Channel[Channel] IN {"Store", "Online"}. Combine columns by listing several arguments (they are ANDed) or by using a table filter.

Table filter arguments and FILTER

When a condition needs a measure or two columns together, pass a table: CALCULATE([Total Sales], FILTER(ALL(Product[Brand]), [Units Sold] > 1000)). FILTER iterates a table row by row, so give it the smallest table that works (a single column through ALL or VALUES, not the fact table). CALCULATE(COUNTROWS(Sales), Sales[SalesAmount] > 10000) is better than COUNTROWS(FILTER(Sales, Sales[SalesAmount] > 10000)) because the column filter is compiled efficiently.

Removing filters: ALL, REMOVEFILTERS, ALLEXCEPT, ALLSELECTED

% of Total = DIVIDE([Total Sales], CALCULATE([Total Sales], REMOVEFILTERS()))   -- ignores everything
% of Selected = DIVIDE([Total Sales], CALCULATE([Total Sales], ALLSELECTED(Category)))
% of Category = DIVIDE([Total Sales], CALCULATE([Total Sales], REMOVEFILTERS(Subcategory)))   -- keeps Category filter

REMOVEFILTERS() with no arguments removes every filter in the model, but keep in mind that it also removes the filters a user chose in slicers.

KEEPFILTERS

CALCULATE([Total Sales], KEEPFILTERS(Category[Category] = "Computers")) intersects the new filter with the existing one instead of replacing it, so categories other than Computers show blank. Use it when the incoming selection must still count, and inside SUMMARIZECOLUMNS and iterator tables so a filter does not widen the rows.

VALUES, SELECTEDVALUE, HASONEVALUE, ISFILTERED

USERELATIONSHIP, CROSSFILTER, TREATAS

Evaluation order and common mistakes

Watch out

Context transition on a table with duplicate rows can double count, because each duplicate row's filter matches both. Make a unique key or avoid CALCULATE in the iterator.

Want to see it run?

Open a finished Power BI project or Excel workbook built on the same data, or ask an AI assistant that has read these guides.