REMOVEFILTERS() in DAX - Excelerator BI

REMOVEFILTERS() in DAX

Level: Intermediate

Recently Microsoft introduced a new function in DAX called REMOVEFILTERS(). This is a very useful and well named function and it does exactly what its name suggests. Its purpose is to act as a table filter parameter inside CALCULATE() as shown in the following example.

Total Sales All Products REMOVEFILTERS() = CALCULATE([Total Sales],REMOVEFILTERS(Products))

Those of you that have been writing DAX for some time will recognise that you have been able to write a similar formula to this forever, as follows:

Total Sales All Products ALL() = CALCULATE([Total Sales],ALL(Products))

Both of these formulas produce identical results – see below.

In fact, under the hood, both of these formulas are indeed identical.

Syntax Sugar

Microsoft has a concept in the DAX language called syntax sugar. I have written about that before in this article here. DAX can be quite complex for beginners yet Microsoft wants DAX to be accessible to any competent Excel user.  Sometimes Microsoft creates syntax sugar versions of functions for the sole purpose of making DAX easier to read, write and understand. Consider the following 2 formulas.

Total Bike Sales Syntax Sugar = CALCULATE([Total Sales],Products[Category]="Bikes")
Total Bikes Sales Full Version = CALCULATE([Total Sales],
     FILTER(ALL(Products[Category]),Products[Category]="Bikes"))

Now assuming you are just learning DAX, which one would you find easier to read, write and understand? I am sure you will agree the first is much easier. But the first formula is simply syntax sugar for the full version shown second.  These 2 formulas are executed identically by the DAX engine.  Every DAX formula is “compiled” by the DAX engine prior to execution, and the syntax sugar version of the formula is converted to the full version prior to execution.

ALL() Has Two Roles in Life

ALL() is a very versatile function – it actually has 2 distinct roles in the DAX language. ALL() can be used as a table function, e.g.

Total Large Customers = COUNTROWS(FILTER(ALL(Customers),[Total Sales]>=2000))

And also as a filter modifier inside CALCULATE()

Total Sales of All Products = CALCULATE([Total Sales],ALL(Products))

I seem to remember someone telling me (Rob Collie I think) that there was quite some discussion internally at Microsoft if there should be one function or two, and the decision was one. I initially learnt DAX from Rob Collie, and he taught me that when using ALL() as a filter modifier inside CALCULATE() you should consider it as the ‘remove filters’ function. Now Microsoft has formalised that concept by introducing the REMOVEFILTERS() function as syntax sugar for that specific use of ALL().

REMOVEFILTERS() is therefore syntax sugar for ALL() when used as a filter parameter inside CALCULATE. It is not a substitute for ALL() when used as a table function. Therefore, this is valid:

Total Sales of All Products = CALCULATE([Total Sales],REMOVEFILTERS(Products))

But this next formula is not valid:

Total Bikes Sales Invalid DAX = CALCULATE([Total Sales],
     FILTER(REMOVEFILTERS(Products[Category]),Products[Category]="Bikes"))

REMOVEFILTERS() Syntax

REMOVEFILTERS() has the same syntax as ALL().  You can pass a table, or 1 (or more) columns, or nothing as the parameter(s).

Total Sales All Products = CALCULATE([Total Sales],REMOVEFILTERS(Products))
Total Sales All Coloured Products = CALCULATE([Total Sales],REMOVEFILTERS(Products[color]))
Total Sales All Colours and Category Products = CALCULATE([Total Sales],
     REMOVEFILTERS(Products[colour],Products[category]))
Total Sales of Everything = CALCULATE([Total Sales],REMOVEFILTERS())

What About The Other ALL() Variants?

ALL() has a few cousins in the DAX language, including ALLEXCEPT() and ALLSELECTED(). These functions do slightly different things to ALL() but they also operate as both table functions and filter modifiers inside CALCULATE(). As of this writing, there is no equivalent syntax sugar for these in the form REMOVEFILTERSEXCEPT() and REMOVEFILTERSSELECTED(), and I doubt there ever will be.

Also note, at this writing, REMOVEFILTERS() is not available in Power Pivot for Excel.

Does REMOVEFILTERS() Make it Easier?

I am interested to know what you think.  Do you think the introduction of the REMOVEFILTERS() function as syntax sugar for ALL() as a filter function makes the language easier to learn?  Let me know in the comments below.

15 thoughts on “REMOVEFILTERS() in DAX”

  1. I’ve understood that ALL can return a table while REMOVEFILTERS can’t.

    From a beginner point of view, REMOVEFILTERS is more explicit than ALL in my opinion.
    Maybe IGNOREFILTERS (this doesn’t exist) would have been a better name, 🙂

    Thanks for this article.

  2. As far as I have reviewed ALL as expression DAX and REMOVEFILTERS as modifier of CALCULATE by semantics and by optimization they would have the same results.

  3. If a report is set up using the filter panel instead of slicers, will these filtering functions (ALL, REMOVEFILTERS, FILTER, ALLSELECTED, etc.) work as expected? For example if reporting percentages will the denominator calculate correctly?

  4. If you’re just starting to learn DAX REMOVEFILTERS definitely is a godsend. It’s much easier to explain than ALL. As a matter of fact, I doubt that most people new to DAX will be able to explain the logic behind using ALL both as a filter remover and table returning function even though the logic behind is very stringent.

  5. Just your explanation that RemoveFilters() and All() are essentially the same thing helps me still get a deeper understanding of All. Even though I use All all the the time (no pun intended) and have created many reports. For me understanding DAX to its full depth is a never ending process. Every little bit helps. Power BI is still in its infancy. There will be many new users that come along that will utilize RemoveFilters(). It will help them understand the All/RemoveFilters application of DAX faster. So ultimately if feels like a good thing.

  6. I think this is not a big progress in the readability of DAX. ALL() isnt that difficult to understand at all.
    Moreover: the fact that REMOVEFILTERS() is not implemented in Excel is a bad thing.
    I think it is far for important that Power Bi and Excel use the same sematics and engines on DAX (and M as well)

  7. I think it’s a waste of time by Microsoft that will only confuse users; As you wrote at some level Dax formulas become different from Excel formulas and become less intuitive.
    Still competent or rather intermediate Excel and Dax users can learn to accept the (rather than understand) them.
    I find ALL() as perhaps the most used function in the “intermediate level” (Most if not all are when ALL() is used like REMOVEFILTERS() ).
    I have implemented well over a thousand Dax measures with ALL() in literally hundreds of projects and explained what it does to dozens of “intermediate users”..
    It works, it is understood and its good.
    Syntax Sugar is a good idea and probably if it was introduced some years ago and would not be 13 characters long, it would be preferable, but at this stage having 2 names (both intermediate level of understanding) and one of them so long only makes it harder for users to understand what another person wrote.
    Language is something that evolves not always in the most logic way (think how much of the English or every other language dos not make sense still one hardly changes it), so should DAX be. This new function only makes it harder to grasp.

    All this is my subjective thoughts, but the fact that it works in Power BI and not in Power Pivot is the big problem: Power BI is running away from Power Pivot as if it is attempting to kill the other. And this is a BIG MISTAKE

  8. Hi there. This is something that Alberto and Marco have been postulating for a long time 🙂 They’ve always wanted a function/modifier with this exact name to be used in place of ALL() when used in CALCULATE(TABLE) as filter remover. They’ll be beside themselves with joy, I guess 🙂

    1. Nope, you are not missing something. You can use ALL, or REMOVEFILTERS. Just like you can use SUM(Table[Column]) or SUMX(Table,Table[Column]). It is all just syntax sugar to make the language easier to understand and use

  9. I’m not sure that it adds or detracts to the DAX lexicon. Those that are used to All() will continue to use it as both the Calculate and Table modifier function. While some may find using Removefilters() as the Calculate modifier and All() as the table modifier adds clarity.

    However, there is some incongruity when, Allselected(), Allexcept(), Allcrossfiltered() and Allnoblankrow(), part of the All() family of functions, remain both calculate and table modifiers.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top