[Power BI] The True Identity of the DAX ALL Function: How is Filter Ignoring Actually Accomplished?


When you first encounter Power BI, ALL feels quite simple. We are taught that it is just a "function that removes all filters." However, in the complex world of practical DAX expressions, ALL takes on two completely different identities depending on its role:

Using All filter in filter modifier vs. Table provider
  • Partner of CALCULATE: A Filter Modifier.
  • Used in Iterators (SUMX, FILTER) or where a Table Expression is required: A Table Function.

At a glance, both seem to "remove filters," making it easy to think they operate identically. However, the internal mechanics are completely different. One changes the rules of calculation, while the other physically generates and passes a full list of data. The true identity of ALL begins to reveal itself the moment you distinguish between these two usage patterns. Today, based on these two structures, let's clarify what the ALL function exactly does.


 

1. A Familiar Misconception about ALL: "A function that ignores filters"


In my early days, I understood ALL only as "the function you use when you want to ignore filters." For example, imagine a sales table with various stores. To calculate each store's sales share, we create a measure:

Table - Stpre & Sales

To Calculate proportion (removed filters/ignoring)

    Sales Share =
    DIVIDE( [Total Sales],
        CALCULATE([Total Sales], ALL(‘slaes'[Store]))
        )

This measure ensures that even if a specific store is selected in a slicer, the denominator always maintains the grand total sales. This is because the ALL inside CALCULATE says, "Ignore the currently applied Store filter." At that time, I thought ALL was simply a logical feature to ignore filters. Whenever I felt that a "number should not fluctuate," the following formulas naturally took place in my mind:

Ignore slicers & Remove filters & Create Baseline
  • ALL = Ignore Slicers: Use when creating values unaffected by slicers.
  • ALL = Filter Removal: Use when calculating shares against the total.
  • ALL = Creating an Absolute Baseline: Use when creating a reference line independent of selections.
This thinking is not wrong. However, if our thoughts stop there, we only understand half of ALL. This is because ALL doesn't actually just "ignore" filters—it is a function that re-supplies the entire table with filters removed. Understanding this difference is the moment you begin to see the true structure of DAX.


 

2. The Essence of ALL is One, but the Engine's Usage is Two


ALL always outputs the entire table with filters removed, but the engine's role changes completely depending on where this table is delivered.


2.1 ALL within CALCULATE


CALCULATE( [Total Sales], ALL(‘Products’[Store]))

In this case, the engine understands: "Remove the currently applied Store filter and use this entire list as the new filter criteria." Here, ALL is used as a tool to reset the Filter Context (the background rules of calculation).

There is no physical iteration here. Only the filter conditions of the query change. Therefore, it is fast. This pattern is the most efficient for most "share calculations." In this context, ALL functions as a Filter Modifier.

2.2 ALL within SUMX: A Table Provider (Physical Iterator Table)

SUMX( ALL('Sales'[Date]), [Total Sales] )

SUMX requires an actual table as its first argument. Therefore, ALL must physically create and pass the entire list of unique values for that column. The engine spreads that table out and calculates it row by row.

Here, ALL is not a filter modifier, but the Iterator Table (the foundation of iteration). This is where performance issues can begin. If there are 10 million rows, the engine might have to expand and calculate all 10 million rows. It's the same ALL, but it operates with a completely different weight.

 

3. Completion of 'Filter Ignoring' is Summoning the 'Original Table'


Filters Bypassed

Many wonder, "If it's about ignoring filters, why do we keep talking about injecting tables?" The reason is clear. The only way to perfectly ignore a filter is to "newly bring in the original table without any constraints." If you break down the operation of ALL(Table[Column]) step-by-step, the logic becomes clear:
  • Filter Removal: It neutralizes all constraints applied by external slicers or visual elements.
  • Data Summoning: It scrapes the data for that column in a state where there are zero constraints.
  • Result: Since there are no conditions, the "entire unique value list of that column" naturally emerges.

Ultimately, "Ignoring Filters" becomes synonymous with "The original table itself without any conditions." Viewed from this perspective, it is not incorrect to understand it as filter ignoring.


 

4. Three Patterns of ALL Encountered in Practice


Once the structure is visible, the patterns become clear. ALL performs physical or logical operations inside the engine according to these specific patterns:


① CALCULATE + ALL: "Filter Modifier (Logic)"


CALCULATE([Measure], ALL(Table[Column]))

  • Operation: It ignores existing slicer or filter conditions and replaces the filter with the "full list" brought by ALL.
  • Characteristics: It is very fast and lightweight because it doesn't physically load a table into memory row-by-row but simply modifies the filter condition statement of the query.
  • Pro Tip: In this pattern, the use of REMOVEFILTERS is strongly recommended. While ALL is a function that returns a table, REMOVEFILTERS is a dedicated command with the sole intent of "filter removal," enhancing code readability and stability.
  • Recommended Usage: % of Total calculations, Slicer-independent fixed values, Absolute baselines.


② SUMX (Iterator) + ALL: "Virtual Table Creation (Physical)"


SUMX(ALL(Table[Column]), [Measure])
  • Operation: SUMX requires a 'physical table' as its first argument. Thus, ALL physically creates and passes a list (table) of all unique values in that column to memory.
  • Performance Warning: Using ALL(Table) in its entirety on 10 million rows will create a performance bomb by unpacking 10 million lines. Conversely, ALL(Table[Column]) is much more efficient as it only iterates as many times as there are unique values with duplicates removed.
  • Recommended Usage: SUMX, AVERAGEX, RANKX, etc., logic that needs to traverse the entire list, and full-dataset inspections independent of filters.

③ FILTER + ALL: "Virtual Table Creation (Physical)"


This combination is used when setting up a 'new stage' that meets specific conditions. FILTER(ALL(Table[Column]), [Condition])
  • Operation: FILTER is also an iterator. It unpacks the entire bundle brought by ALL and creates a new virtual table containing only the rows that meet the condition.
  • Characteristics: This is not just ignoring a filter, but a physical process of creating a "new list consisting only of those that meet certain criteria among the whole."
  • Recommended Usage: Extracting top sales from the whole, redefining sets that satisfy specific criteria.

Summary Guide: Comparison at a Glance


Summary Guide: Comparison at a Glance

 

5. The True Identity of ALL: Rebuild, Not Remove


Now we can define ALL like this: ALL is not a Remove function (a function that erases), but a Rebuild function (a function that resets). Its purpose is not to simply eliminate existing filters, but to re-establish a new standard called the "entire set."

And when that standard:
  • Enters CALCULATE, it changes the calculation rules.
  • Enters SUMX/FILTER, it becomes an actual task list.

It’s the same function, but it creates completely different engine mechanisms.


 

Wrapping up



ALL is a Context Control Tool

ALL is not just a filter-blocking function. It is a tool for resetting calculation standards and a function that physically supplies the entire set if necessary. The moment you understand ALL, DAX moves from memorizing functions to the realm of context design.

Was this helpful? In the next post, we will cover how ALL is used in practice. If you have any questions, please leave them in the comments!


Comments

Post a Comment

Popular posts from this blog

DAX CALENDAR Function Deep Dive and Practical Usage Guide

Image
The CALENDAR function is essential for creating a date table in a data model, and it plays a vital role when performing time intelligence calculations. Through this document, you'll be able to get a detailed understanding of the CALENDAR function's syntax, how it operates, and how you can leverage it in real-world data analysis scenarios.   1. What Exactly is the CALENDAR Function? The CALENDAR function in DAX is basically your go-to tool for building a dedicated date table based on a specific date range. All you do is give it a start date and an end date, and it spits out a single-column table containing every single date within that range. This generated table is what we commonly call a 'Date Dimension Table. ' It's absolutely critical for your data model because you'll use it to set up relationships with other tables (like sales, orders, or inventory) and, more importantly, it becomes the foundation for all your time-based analysis.     1.1. Syntax ...
Read more

Standard Deviation (Part 2): Strategic Limitations and Complementary Perspectives in Standard Deviation Analysis

Image
As we've seen, standard deviation is more than just measuring 'how spread out the data is' it quantifies that spread to evaluate the stability and risk of business performance, thus serving as a key managerial analysis metric that supports strategic decision-making. However, relying solely on standard deviation for interpretation can easily lead to misjudgment. When using standard deviation for corporate, sales/profit analysis, or forecasting, the following additional factors must be considered: If you missed 'Standard Deviation' Previous contents, Please check out the below link. Standard Deviation The Complete Guide to the Core of Business Data Analysis Standard Deviation (Part 1): Measuring Data Volatility and Using the Insights for Better Strategy   1. Considering the Relationship with the Mean (Expected Value) Standard deviation represents only the absolute degree of data 'spread.' Therefore, to grasp the relative meaning of this figure, it must be cons...
Read more

Standard Deviation (Part 1): Measuring Data Volatility and Using the Insights for Better Strategy

Image
Standard Deviation is a core metric that quantifies the 'dispersion' of your data, showing—as a single number—how tightly clustered or widely spread the data points are from the mean. A larger number means the data is widely spread out, while a smaller number means it's tightly grouped. Let's explore how this concept is used strategically in analyzing sales and profits. If you missed 'Standard Deviation' first contents, Please check out the below link. Standard Deviation The Complete Guide to the Core of Business Data Analysis A nd you can also refer next contents, after reading this. Standard Deviation (Part 2): Strategic Limitations and Complementary Perspectives in Standard Deviation Analysis   1. Why We Must Read the 'Wobble' (The Trap of the Mean) It's natural to first look at average sales or profit when analyzing a company's performance. The average is the central value of the data, making it the most fundamental and useful metric for gr...
Read more