DAX Deep Dive 04 : Why Do Pros Choose REMOVEFILTERS Over ALL?
What distinguishes a "pro" from an "intermediate" user in Power BI? It is whether they understand and use functions based on their intrinsic roles.
While many users try to handle both filter removal and table creation using only the ALL function, in the world of large-scale models, this slight ambiguity leads to maintenance nightmares and performance bottlenecks. In 2019, Microsoft introduced the dedicated tool REMOVEFILTERS for very clear reasons:
- Intent: Is the calculation logic immediately clear upon reading the code?
- Structure: Is it preventing the unnecessary creation of virtual tables?
- Performance: Can the engine modify the filter context in the most efficient way?
1. Historical Background: An Evolution That ALL Could Not Handle Alone
1) ALL – The "Solitary Founding Member" of the Power Pivot Era
The ALL function was introduced with the birth of Power Pivot in 2010. Back then, the DAX ecosystem was much simpler. Since there was no dedicated concept for "only" removing filters, ALL had to carry the entire burden like a young breadwinner. It handled everything from calculating grand totals and ignoring slicers to creating unique value tables for ranking. During this time, ALL was less of a precision-engineered part and more like the "Swiss Army Knife of DAX" that you just plugged into every situation.2) Power BI Expansion and the Revealed Limitations
As data models became more sophisticated and Fact tables grew beyond millions or tens of millions of rows, the versatility of ALL began to act as a poison. Every time someone (including your future self) encountered ALL, they had to ask: "Is this ALL intended simply to clear a filter?" "Or is it intended to virtually generate a physical total table to be used in a calculation?" The inefficiency of having to reinterpret the author's intent every single time before even running the code led to rising maintenance costs.3) REMOVEFILTERS – Complete Separation of 'Intent' and 'Function'
Amidst this confusion, REMOVEFILTERS finally emerged as a relief pitcher in 2019. The purpose of this function is singular: to completely separate the pure act of "filter removal" from the physical function of "table creation." This was not just the addition of another function. It was a major milestone symbolizing the evolution of DAX design philosophy from "code that just works" to "design where intent is clearly readable."
2. Ambiguity of Multi-purpose Functions vs. Clarity of Dedicated Tools
The quality of your code changes depending on "which function" you use, even if the result is the same. Let's look at the details the pros focus on through an example of calculating a "Ratio to Total Sales."
1) Using ALL: "Is it an Eraser or a Basket?"
ALL is still powerful today, but it inherently has two faces.Ratio =
DIVIDE(
[Total Sales],
CALCULATE( [Total Sales], ALL(Products) )
)
When you encounter code using ALL inside CALCULATE, a colleague (or your future self) must subconsciously pause and judge:
- The Eraser: "Does this want to ignore the filters applied to the Products table?"
- The Basket: "Does this want to use all rows of the Products table as physical filter values?" Because one function performs both purposes, more energy is wasted figuring out the designer's intent as the model gets more complex.
2) Using REMOVEFILTERS: "A Clear, Dedicated Eraser"
On the other hand, REMOVEFILTERS is a function that clearly debuted to play only the "eraser" role.Ratio =
DIVIDE(
[Total Sales],
CALCULATE([Total Sales], REMOVEFILTERS(Products))
)
REMOVEFILTERS does not return data or a table. It only gives a command to the engine: "Ignore the filters on this column!"
- Memory Efficiency: Does not create unnecessary virtual tables.
- Semantic Clarity: Immediately recognized as a "filter removal" act upon seeing the code.
3. Performance and Memory: The Gap in Large-Scale Models
The difference is negligible in small datasets, but the story changes in large-scale models with millions of records.
1) Unnecessary Overhead of ALL
ALL is fundamentally a function that returns a table. Even when inside CALCULATE, the engine internally evaluates the current filter context, constructs the entire table in memory, and then passes that table as a filter argument. Especially when applying ALL to high-cardinality columns (like Customer Name or SKU) or when measures are nested, this unnecessary table creation leads to memory pressure and increased CPU cost.
2) Optimization of REMOVEFILTERS
REMOVEFILTERS does not generate new data. From the engine's perspective, it is closer to a simple "context modification directive." - No additional memory allocation.
- Minimizes the burden on the VertiPaq engine.
- Handled lightly at the Formula Engine stage.
4. Then When Should We Use ALL?
Just because REMOVEFILTERS is better for its purpose doesn't mean ALL is retired. ALL shows its true strength "when the table itself is needed."Creating New Tables: When a list with duplicates removed is needed, such as NewTable = ALL(Products[Category]).
Use inside Iterators: When you need to ignore the filter context and iterate through all rows one by one, such as SUMX(ALL(Sales), ...).
Comparison at a Glance: ALL vs. REMOVEFILTERS
Wrapping up
The standards of the pros are clear:
- Use ALL only when you need the data (a list). (e.g., creating a slicer list with ALL(Category))
- Use REMOVEFILTERS for all other filter control scenarios.
This small difference evolves your DAX from simple numbers into scalable data design. Why not try changing the REMOVEFILTERS inside your CALCULATE starting today?
Did you find this post helpful? In the next session, we will cover why ALLSELECTED is often called the "hell" for practitioners and the mysterious principles of its filter restoration. If a specific DAX expression you are currently writing is causing performance issues, please leave a comment and we can brainstorm together!
<Other posts on the blog>
Comments
Post a Comment