REMOVEFILTERS() in DAX

Recently Microsoft introduced the DAX REMOVEFILTERS() new function. 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 function REMOVEFILTERS() in DAX as syntax sugar for that specific use of ALL().

DAX 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")
       )

DAX REMOVEFILTERS() Syntax

REMOVEFILTERS() in DAX 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 DAX REMOVEFILTERS () function as syntax sugar for ALL() as a filter function makes the language easier to learn?  Let me know in the comments below.