DAX ALL vs ALLEXCEPT

ALL and ALLEXCEPT are two essential DAX functions that developers frequently use throughout their journey. Unlike the FILTER function, which is designed to exclude certain records from a calculation, ALL and ALLEXCEPT are used to include rows by removing filters from specific columns or tables, thus expanding the calculation context.

Although both functions aim to include rows, each behaves differently and is best suited for specific scenarios.

A typical example where ALL is useful is when calculating the percentage contribution of sales by category. In this case, we would have a table with a Category column and a Sales column, along with a third column called Total Sales, which returns the overall sales regardless of the category. Sales and Total Sales would then serve as the numerator and denominator, respectively, in the Sales Contribution calculation.

As you may notice, the Sales and Total Sales measures share very similar DAX scripts—the only difference is the use of the ALL function in the Total Sales measure. In this context, ALL removes all implicit and explicit filters applied to any column in the Sales table (explicit filters are those intentionally applied to the report or page, while implicit filters are automatically applied by the visual—in this case, the implicit filter is the Category dimension which creates the breakdown between categories).

Finally, the Sales Contribution measure is calculated using the DIVIDE function, which takes a numerator and a denominator as inputs. It returns the result of the division as a decimal number, which is then formatted as a percentage.

Sales = SUMX('Sales', Sales[Sales])

Total Sales = SUMX(ALL(Sales), Sales[Sales])

Sales Contribution = DIVIDE([Sales], [Total Sales])

In the example above, we passed the Sales table as the argument to the ALL function. However, ALL also accepts a column or a set of columns as its argument. For instance, we can achieve the same result using a column as the argument, as shown in the figure below, but with some differences:

To reproduce the same outcome using a column instead of a table indeed, we must explicitly specify which column's filter we want to ignore—in this case, is the Category Name column from the Categories table, since it’s the one creating the breakdown in the visual and so we would have a measure like the following:

Total Sales - ALL(Column) = SUMX(ALL(Categories[Category Name]), [Sales])

As a result, if we apply a filter on another column like Category ID, the Total Sales - ALL(Column) measure will respond to changes in that filter, whereas the Total Sales - ALL(Table) measure will remain unaffected—as shown in the table below.

The last scenario we’ll cover in this article involves the ALLEXCEPT function. This function behaves in a way that is essentially the reverse of ALL(Column). While ALL(Column) removes filters only from the column(s) specified in its argument, ALLEXCEPT does the opposite—it removes filters from all columns except the one(s) passed as argument(s). the following code indeed, produce the same result as ALL(Column) did:

Total Sales - ALLEXCEPT(Column) = 
    SUMX(
        ALLEXCEPT(
            Categories, 
            Categories[Category ID]), 
    [Sales]
    )

An interesting aspect to take into consideration when writing a DAX code is how ALL(Column) and ALLEXCEPT(Column) behave when additional columns are added to the table. In such cases:

• ALLEXCEPT(Column) will automatically ignore any filters applied to newly added columns (unless they are explicitly included in the arguments).

• ALL(Column), on the other hand, will respond to filters on any new columns, since it only removes filters from the specific column(s) passed to it.

  
  

This makes ALLEXCEPT(Column) particularly useful when you want to preserve grouping by one or more columns while removing all other filters dynamically—even as the model evolves.

DAX ALL vs ALLEXCEPT - Conclusion

To summarize, the table below outlines how filters (implicit and explicit) behave when using the functions ALL(Table), ALL(Column), and ALLEXCEPT(Column).DAX ALL vs ALLEXCEPT

Additional note - Behaviours in Row Context

As additional note, I would also pointed out how this three function behave in a row context - if you don't know much about evaluation context (which is probably one of the most if not the most important concept of DAX), stay tuned as I'll publish an article very soon about it. Since they are table functions, they cannot be used on their own in measures—because they return a table, not a scalar value. However, they can be used directly in calculated tables within the Table View.

For example, if we create three calculated tables using the following functions, each will return a different result:

All (Table) = ALL(Categories) 

All (Column) = ALL(Categories[Category Name])

Allexcept (Column) = ALLEXCEPT(Categories, Categories[Category Name])

As you might have noticed, all the queries return only the columns for which filters are intended to be removed—ALL(Categories[Category Name]), for example, returns just the Category Name column. At first glance, this behavior might seem unusual to someone with a SQL background, who could assume that the best way to remove filters is simply by excluding the column entirely from the query.

However, DAX operates at a semantic level, not at the physical level of the table structure. This means that filters will still be applied—or removed—based on the logical relationships in the data model, regardless of whether the column is physically present in the virtual table returned by the function.

So, what's the advantage of passing only the Category Name column to the SUMX function in the example below?

Total Sales - ALL(Column) = SUMX(ALL(Categories[Category Name]), [Sales])

The answer is quite straightforward. For instance, let’s say we have an explicit filter on the visual that limits Category to only Cameras and Gaming Consoles.When we use ALL(Categories[Category Name]), it returns a list of all distinct category values, not just the visible ones— soCameras and Gaming Consoles as well as all other categories that were filtered out. This effectively ignores the filter applied in the visual.

Then, SUMX iterates over each of these categories, calculates the sales for each one, and finally adds them all together to return the total value across all categories, not just the ones included in the current filter context.

---

Thanks for being a part of our community!

If you found this article helpful and would like to show your support, don’t hesitate to:

1. Clap on this story

2. Leave a comment below telling me what you think. This will help me with the next articles

3. Support my work on Buy Me a Coffee ☕️

These actions really really help me out, and are much appreciated!

Follow me for more insights on LinkedIn | YouTube | Medium