Physical Address
Haryana ,India
Introduction
When working in Power BI, understanding DAX filter functions can be a game-changer for building accurate and interactive reports. The functions ALL, ALLEXCEPT, and ALLSELECTED are three frequently used yet misinterpreted functions.
Although they sound similar, they behave differently when it comes to clearing or keeping filters.
In this article, we’ll break them down in simple words with clear examples so you can confidently use them in your reports.
1. What is ALL in DAX?
ALL removes all filters from the specified table or column.
It’s like telling Power BI: “Ignore any filters and show me everything.”
Syntax:
ALL ( [<tableNameOrColumnName>] )Example:
Suppose you have this table:
| Product | Sales |
| A | 100 |
| B | 200 |
| C | 300 |
If a slicer filters to Product A, normally:
Total Sales = SUM(Sales[Amount])
would give 100.
But:
Total Sales ALL = CALCULATE(SUM(Sales[Amount]), ALL(Sales))returns 600, ignoring the slicer.
Use Case:
Calculate grand totals or percentages that should not be affected by slicers or filters.
2. What is ALLEXCEPT in DAX?
ALLEXCEPT removes all filters except those you want to keep.
Think of it as saying: “Clear all filters, but keep this specific filter.”
Syntax:
ALLEXCEPT ( <table>, <column>[, <column>[, …]] )Example:
Suppose you have:
| Product | Region | Sales |
| A | East | 100 |
| A | West | 200 |
| B | East | 300 |
If you want totals per product regardless of region filters:
Sales by Product = CALCULATE(SUM(Sales[Amount]), ALLEXCEPT(Sales, Sales[Product]))Even if you filter Region to East, this still sums all regions for each product.
Use Case:
Group totals while ignoring other filters (e.g., keep Product filter, remove Region filter).
3. What is ALLSELECTED in DAX?
ALLSELECTED removes filters inside the visual but keeps outer context filters like slicers.
It’s comparable to stating: “Ignore the breakdown in this chart but respect the user’s selection elsewhere.”
Syntax:
ALLSELECTED ( [<tableNameOrColumnName>] )Example:
You have a slicer selecting only Products A & B.
In a chart showing Product-wise sales, if you click on Product A, normal SUM would give just A’s value.
But:
Total Sales ALLSELECTED = CALCULATE(SUM(Sales[Amount]), ALLSELECTED(Sales[Product]))returns total sales for both A & B from the slicer, ignoring the visual’s filter on just A.
Use Case:
Percentage of total based on slicer selection, useful for dynamic visuals.
Quick Comparison Table
| Function | Clears Filters From | Keeps Specific Filters? | Keeps Slicer Selections? | Best For |
| ALL | Everything | ❌ No | ❌ No | Grand totals ignoring all filters |
| ALLEXCEPT | Everything except given columns | ✅ Yes | ❌ No | Group totals with some filters kept |
| ALLSELECTED | Visual-level filters | ✅ Yes | ✅ Yes | % of total within slicer selection |
Conclusion
While ALL, ALLEXCEPT, and ALLSELECTED all deal with filters, they serve different purposes:
- ALL – Ignores everything.
- ALLEXCEPT – Ignores everything except what you choose.
- ALLSELECTED – Ignores visual filters but keeps slicer context.
Mastering these functions will make your Power BI reports more interactive, dynamic, and accurate.
FAQs
- Which is faster, ALL or ALLEXCEPT?
ALL is slightly simpler, but performance difference is negligible for small datasets. - Can ALLSELECTED work without slicers?
Yes, but it behaves similarly to ALL in that case. - When should I avoid ALL?
When you need user context in calculations—ALL will remove it entirely. - Is ALLEXCEPT only for one column?
No, you can keep multiple columns’ filters. - Do these functions work in Excel Power Pivot?
Yes, they are also available in Excel’s DAX environment.
