DAX CALCULATE and filter modifiers: ALL, REMOVEFILTERS, KEEPFILTERS and more

CALCULATE evaluates an expression in a modified filter context. It is the function behind filtered measures, percent-of-total measures and alternative relationship paths, and it is the most important DAX function for the analyst to read fluently.

Syntax and filter arguments

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

Filter arguments can be:

• Boolean expressions, such as ‘Product’[Color] = “Blue”. A Boolean filter can reference columns from only one table, can’t reference a measure, can’t contain a nested CALCULATE, and can’t use functions that scan or return a table (except inside an aggregation).
• Table expressions, such as FILTER(…) or VALUES(…), for conditions a Boolean can’t express, including conditions on measures.
• Filter modifier functions that remove filters, keep filters or change relationships.

Several filter arguments combine with AND. Within one argument, || (OR) and && (AND) can be used.

Without KEEPFILTERS, each filter argument behaves as follows: if the column isn’t already filtered, the filter is added; if it is filtered (by a slicer or the visual), the new filter replaces the existing one on that column. Filters on other columns stay in place.

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

In a visual grouped by colour, every row of Blue Sales shows the blue total, because the filter argument overwrites the row’s colour filter. Wrapping the condition in KEEPFILTERS intersects it with the existing filter instead, so only the Blue row shows a value.

Boolean filters versus FILTER

Microsoft recommends Boolean expressions as filter arguments whenever possible, because Import tables are column stores optimised for column filters. FILTER iterates every row of its table; use it only when the condition involves a measure, compares columns, or needs OR logic across columns that a Boolean argument can’t express.

Sales in Profitable Months =
CALCULATE (
    [Total Sales],
    FILTER ( VALUES ( 'Date'[Month] ), [Profit] > 0 )
)

FILTER is required here because the condition tests a measure ([Profit]), which a Boolean filter argument can’t reference.

Filter modifier functions

Function	Effect inside CALCULATE	Typical use
REMOVEFILTERS(table or columns)	Clears filters; can’t return a table	Denominator of a percent of total
ALL(table or columns)	Clears filters; also usable as a table function	Grand total ratio, or iterating all rows
ALL() with no argument	Clears all filters everywhere	Overall total regardless of any filter
ALLEXCEPT(table, column, …)	Clears filters on every column of the table except those listed	Keep one grouping while removing others
ALLSELECTED(table or column)	Removes filters from inside the visual’s query but keeps outer filters (slicers, page and report filters)	“Visual total” percentages that respect slicers
KEEPFILTERS(filter)	Intersects the filter with existing filters instead of overwriting	Filtered measures that still respond to the visual’s grouping
USERELATIONSHIP(col1, col2)	Engages an inactive relationship; the active one is ignored for this calculation	Ship date or due date measures on a role-playing date table
CROSSFILTER(col1, col2, direction)	Changes direction to Both, OneWay or None for this calculation	Dimension-to-dimension counts without a bi-directional model relationship

Where the tool supports it, REMOVEFILTERS is the clearer way to remove filters; ALL behaves both as a filter modifier and as a table-returning function. ALL takes base table or column references only, not expressions.

Percent of total patterns

Sales % of All Channels =
DIVIDE (
    [Total Sales],
    CALCULATE ( [Total Sales], REMOVEFILTERS ( 'Sales Order'[Channel] ) )
)

The denominator removes only the Channel filter, so each channel is compared with all channels while other filters (year, region slicers) still apply. Changing the denominator’s filter modifier changes the meaning:

Denominator	Each row shows its share of
CALCULATE([Total Sales], REMOVEFILTERS(‘Sales Order’[Channel]))	All channels, within the other current filters
CALCULATE([Total Sales], ALL(Sales))	All sales rows, ignoring filters on the Sales table
CALCULATE([Total Sales], REMOVEFILTERS())	Everything in the model, ignoring every filter including slicers
CALCULATE([Total Sales], ALLSELECTED(‘Product’[Category]))	The categories visible after slicers and page filters (the visual total)

For a matrix with a hierarchy, ISINSCOPE(column) returns TRUE when that column is the current level of grouping, so a measure can pick the right denominator per level:

Share of Parent =
SWITCH (
    TRUE (),
    ISINSCOPE ( 'Product'[Subcategory] ),
        DIVIDE ( [Total Sales], CALCULATE ( [Total Sales], ALLSELECTED ( 'Product'[Subcategory] ) ) ),
    ISINSCOPE ( 'Product'[Category] ),
        DIVIDE ( [Total Sales], CALCULATE ( [Total Sales], ALLSELECTED ( 'Product'[Category] ) ) ),
    1
)

At subcategory level the denominator is the category’s total; at category level it is the visual total; at the grand total the measure returns 1 (100 percent). Test the lowest level first, because a subcategory row is also within a category.

Built-in alternatives exist for simple shares: in a table or matrix (and some charts), a value’s Show value as option displays percent of grand total, and in a matrix also percent of row or column total; visual calculations can compute shares on the visual (Chapter 7). A measure is needed when the calculation must be reusable or must respect specific filters.

Context transition and calculated columns

CALCULATE with no filter arguments still matters: in a calculated column it turns the current row into a filter. In the following Customer column, ALLEXCEPT keeps only the customer key filter, so each customer is classified by its own sales.

Customer Segment =
IF (
    CALCULATE ( SUM ( Sales[Sales Amount] ), ALLEXCEPT ( Customer, Customer[CustomerKey] ) ) < 2500,
    "Low",
    "High"
)

Common trap: Assuming a CALCULATE filter on Product[Color] combines with a slicer on the same column - by default it replaces the existing filter on that column; KEEPFILTERS is needed to intersect them.

Common trap: Using ALL to build a percentage that should respect the user’s slicer selections - ALL ignores slicer filters too; ALLSELECTED removes filters from inside the visual while keeping outer filters such as slicers.

Common trap: Writing CALCULATE([Sales], [Margin] > 0.2) - a Boolean filter argument can’t reference a measure; use FILTER over a column’s VALUES with the measure condition.

Get the whole book

This note is one section of Ultra Transcenders PL-300: Microsoft Power BI Data Analyst, an independent study guide that explains every topic the exam covers by technology, with comparison tables, diagrams and the common traps, plus a glossary linked to Microsoft Learn.

About the book · PL-300 terms in the glossary · All PL-300 study notes