How to Use ALL Function in DAX in Power BI
In Power BI, use the
ALL function in DAX to remove filters from one or more columns or tables, allowing calculations over the entire dataset. It is commonly used to calculate totals or percentages by ignoring slicers or filters applied in reports.Syntax
The ALL function syntax can be used in two main ways:
ALL(TableOrColumn): Removes filters from the specified table or column.ALL(): Removes all filters from all columns in the current filter context.
This lets you calculate values ignoring filters applied in visuals or slicers.
DAX
ALL(<TableOrColumn>)
Example
This example shows how to calculate the total sales ignoring any filters on the Product column.
DAX
Total Sales All Products = CALCULATE(SUM(Sales[Amount]), ALL(Product[ProductName]))
Output
If filtered to only one product, this measure still returns the sum of sales for all products ignoring the filter.
Common Pitfalls
Common mistakes when using ALL include:
- Using
ALLwithoutCALCULATEwhich does not change filter context. - Removing filters unintentionally on columns that affect other calculations.
- Confusing
ALLwithREMOVEFILTERSwhich behaves similarly but has subtle differences.
Always test your measure to ensure it returns expected results.
DAX
Wrong: Total Sales All = SUM(Sales[Amount]) + ALL(Product[ProductName]) Right: Total Sales All = CALCULATE(SUM(Sales[Amount]), ALL(Product[ProductName]))
Quick Reference
| Usage | Description |
|---|---|
| ALL(Column) | Removes filters from a specific column. |
| ALL(Table) | Removes filters from all columns in a table. |
| ALL() | Removes all filters in the current filter context. |
| Used with CALCULATE | Changes filter context to ignore filters for calculation. |
Key Takeaways
Use ALL inside CALCULATE to remove filters and get totals or overall values.
ALL can remove filters from specific columns, entire tables, or all filters in context.
Without CALCULATE, ALL does not affect filter context and won't change results.
Test your measures to avoid removing filters unintentionally.
ALL is useful for calculating percentages or comparisons against total values.