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
- The incoming filter context (slicers, rows, columns) is copied.
- Any row context is converted to filters (context transition).
- Each filter argument is applied: it either replaces, removes or adds to the filters on the columns it names.
- 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.
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
REMOVEFILTERS(Table | Column ...): only valid as a CALCULATE filter modifier; clears the filters. Preferred for readability.ALL(Table | Column ...): same effect inside CALCULATE, and also works as a table function that returns every row or every distinct value, ignoring filters.ALLEXCEPT(Table, Col1, Col2): clears every filter on the table except the columns listed (for "percent of the group" measures).ALLSELECTED(Table | Column): restores the filters that came from OUTSIDE the current visual (slicers, page and report filters) and ignores the visual's own rows and columns. This is the "percent of the selection" denominator.ALLNOBLANKROWleaves out the blank row that a broken relationship can add.
% 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
VALUES(Column)returns the distinct values currently visible in the column (a one-column table, plus a blank row if a relationship has unmatched rows).DISTINCTis the same without that blank row.SELECTEDVALUE(Column, [alternate]): the single visible value, otherwise the alternate (blank by default). The usual tool for slicer-driven logic.HASONEVALUE(Column),ISFILTERED(Column),ISCROSSFILTERED,ISINSCOPE(Column): test what the context holds. ISINSCOPE is the right way to detect which level of a matrix hierarchy a cell is on, to fix subtotals.
USERELATIONSHIP, CROSSFILTER, TREATAS
CALCULATE([Total Sales], USERELATIONSHIP(Sales[ShipDate], Calendar[Date]))makes an inactive relationship active for that calculation only (role-playing dates: order date versus ship date).CROSSFILTER(Sales[ProductKey], Product[ProductKey], BOTH)changes the filter direction for that calculation without making the model bidirectional.TREATAS(VALUES(Budget[Month]), Calendar[Date])applies the values of one column as filters on another with no relationship; it is the way to use a disconnected table or to join on a different grain.
Evaluation order and common mistakes
- A measure reference like
[Total Sales]is secretlyCALCULATE(SUM(...)), so it always converts row context to filters, which is why measures work inside iterators and calculated columns behave differently. - Replacing versus keeping: a Boolean filter replaces, KEEPFILTERS intersects, ALL removes. When a result "ignores my slicer", you removed it; when it ignores the row, you replaced the row's filter.
- Do not put the SAME column in two Boolean filters expecting OR; two arguments are ANDed (no rows). Use IN or a single
||expression on one column:Product[Color] = "Red" || Product[Color] = "Blue"is allowed when it names one column only. - A filter on a dimension column does not reach other dimensions unless the fact table connects them. To count products that were ever sold, filter through the fact table.
- Calculation groups and CALCULATE interact through SELECTEDMEASURE; if a measure ignores a calculation item, check precedence.
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.