REMOVEFILTERS and ALL in DAX: knowing when to use which

Yesterday’s post was about FILTER, the function that hands filter control back to your user. Today we do the opposite and turn filters off where you need to.

That is the spot where REMOVEFILTERS and ALL show up side by side in code examples. They look like they do the same thing, and inside CALCULATE they do, but ALL has a second role as a table function, and that is where the choice sits.

What REMOVEFILTERS does

REMOVEFILTERS is a dedicated modifier for CALCULATE. It only exists inside CALCULATE or CALCULATETABLE, nowhere else. You pass it a table, a column, or a set of columns, and it removes the filter on exactly that thing before the measure is evaluated.

CALCULATE(
    SUM(Sales[Amount]),
    REMOVEFILTERS(Product[Color])
)

What it returns is nothing. REMOVEFILTERS is an instruction to CALCULATE to clear a filter before the calculation runs, and it never produces a table. One job, no dual role.

Where ALL has an extra role

ALL has two faces. Use ALL as a filter argument inside CALCULATE and it does exactly what REMOVEFILTERS does, which is clear the filter on that column or table. Write ALL outside CALCULATE as a table expression, and it returns all rows of the table with filters ignored:

SUMX(
    ALL(Product),
    Product[Price] * Product[Stock]
)

Here ALL works as a table. It gives SUMX a complete list of products to go through row by row, with all filters ignored. REMOVEFILTERS cannot do this, because it is not a table and returns nothing outside CALCULATE. That second use of ALL comes back later in this series, when you need a table inside your calculation rather than a filter instruction.

When they are the same

Inside CALCULATE as a filter argument, ALL(Product[Color]) and REMOVEFILTERS(Product[Color]) are literally identical: both remove the filter on the Color column, with the same result to the decimal and the same performance. There is no reason to choose one over the other on speed or precision grounds.

The difference is in the code that stays behind. If a colleague sees ALL(Product[Color]) in your measure, they have to check which role ALL is playing here: filter-remover or table-returner? If they see REMOVEFILTERS(Product[Color]), they know immediately that a filter is being cleared, end of story. REMOVEFILTERS carries its intention in its name, and ALL does not.

The rule for today

For the filter-removing role inside CALCULATE, REMOVEFILTERS is the clearer choice, precisely because it does not carry that dual role. If you want to turn a filter off, use REMOVEFILTERS. If you need to return a table with all rows, use ALL, and that use comes later in the series.

Practically speaking, clear only the one column you need to free. REMOVEFILTERS(Sales) on the whole fact table removes your year slicer and your region selection too, and that is almost never what you mean:

// Good: only removes the color filter, year slicer stays in place
CALCULATE(
    SUM(Sales[Amount]),
    REMOVEFILTERS(Product[Color])
)

// Watch out: removes all filters on Sales, including the user's year slicer
CALCULATE(
    SUM(Sales[Amount]),
    REMOVEFILTERS(Sales)
)

Use REMOVEFILTERS(Product[Color]) when you only want to turn off the color filter. Your user’s year slicer keeps working as expected.

Tomorrow: percentage of total

Now that you know how to clear a filter and which column to target, the next post covers the first real use case: percentage of total. The denominator of that calculation has to evaluate on a single product while keeping the user’s year slicer intact. That only works if you remove exactly the right filter and nothing more.

This is day 3 of 5 in the DAX filter burst. Day 2 was FILTER as the escape route. Day 4 brings the first measurement where you really need a filter eraser, and the whole series is summarized in the cheat sheet.

Want to work through Power BI and DAX step by step? Head to trainerbjorn.nl for training courses and a free study path for the PL-300 exam.