Lesson 63 Understanding ALL and ALLEXCEPT in DAX: Mastering Advanced Filtering in Power BI

Welcome back to Virvijay.com, your go-to resource for mastering Power BI! 🎯

In our last blog, we explored the CALCULATE function, one of the most powerful DAX functions in Power BI. Today, we will take it a step further and learn about ALL and ALLEXCEPT, which help control and remove filters in your calculations.

By the end of this blog, you’ll learn:

• ✅ What are ALL and ALLEXCEPT functions in DAX?
• ✅ When to use ALL vs. ALLEXCEPT
• ✅ How to remove filters dynamically
• ✅ Real-world examples with DAX formulas
• ✅ Common mistakes and best practices

1️⃣ What is the ALL Function in DAX?

The ALL function removes all filters from a specific column or table. It is useful when you want to ignore existing filters and calculate values for the entire dataset.

📌 Syntax:

DAX

ALL(<Table> | <Column>)

🔍 Key Features:

• Removes all filters from the specified table or column.
• Useful in percentage calculations, rankings, and total values.
• Often used inside CALCULATE for dynamic filtering.

2️⃣ How to Use ALL in Power BI (With Examples)

Example 1: Calculate Total Sales Without Any Filters

📌 Suppose we want to calculate total sales, ignoring any filters on the table.

👉 DAX Formula:

1. DAX
2. Total Sales All = CALCULATE(SUM(Sales[Sales Amount]), ALL(Sales))

🔍 How it works:

1. SUM(Sales[Sales Amount]) → Calculates total sales.
2. ALL(Sales) → Removes all filters on the Sales table.

🚀 Result: No matter what filters are applied in the report, this measure always returns total sales for the entire dataset.

Example 2: Calculate Percentage of Total Sales

📌 Suppose we want to calculate sales percentage for each region.

👉 DAX Formula:

DAX

Sales % of Total = 

DIVIDE(

    SUM(Sales[Sales Amount]), 

    CALCULATE(SUM(Sales[Sales Amount]), ALL(Sales))

)

🔍 How it works:

1. SUM(Sales[Sales Amount]) → Gets sales for the current filter context.
2. CALCULATE(SUM(Sales[Sales Amount]), ALL(Sales)) → Gets total sales ignoring filters.
3. DIVIDE(...) → Computes (Region Sales / Total Sales) * 100%.

🚀 Result: The measure shows percentage contribution of each region’s sales to total sales.

Example 3: Show Maximum Sales Across All Products

📌 Suppose we want to find the maximum sales for any product, ignoring all filters.

👉 DAX Formula:

DAX

Max Sales = CALCULATE(MAX(Sales[Sales Amount]), ALL(Sales[Product Name]))

🔍 How it works:

1. MAX(Sales[Sales Amount]) → Gets max sales.
2. ALL(Sales[Product Name]) → Removes filters from Product Name column.

🚀 Result: This measure returns the highest sales value across all products, even if a filter is applied.

3️⃣ What is the ALLEXCEPT Function in DAX?

The ALLEXCEPT function removes filters from all columns except the ones specified.

📌 Syntax:

DAX

ALLEXCEPT(<Table>, <Column1>, <Column2>, ...)

🔍 Key Features:

• Removes filters from the entire table but keeps specific columns filtered.
• Useful when you want to clear all filters except category, region, or date.
• Ideal for dynamic calculations and comparisons.

4️⃣ How to Use ALLEXCEPT in Power BI (With Examples)

Example 1: Calculate Total Sales While Keeping Region Filter

📌 Suppose we want to calculate total sales per region but ignore all other filters.

👉 DAX Formula:

DAX

Total Sales Per Region = CALCULATE(SUM(Sales[Sales Amount]), ALLEXCEPT(Sales, Sales[Region]))

🔍 How it works:

1. ALLEXCEPT(Sales, Sales[Region]) → Removes all filters except Region.
2. SUM(Sales[Sales Amount]) → Computes total sales.

🚀 Result: The measure shows total sales per region while ignoring all other filters.

Example 2: Calculate Running Total While Keeping Date Filter

📌 Suppose we want to calculate a running total of sales while keeping the Date filter active.

👉 DAX Formula:

DAX

Running Total Sales = 

CALCULATE(

    SUM(Sales[Sales Amount]), 

    ALLEXCEPT(Sales, Sales[Order Date])

)

🔍 How it works:

1. ALLEXCEPT(Sales, Sales[Order Date]) → Keeps only the Order Date filter.
2. SUM(Sales[Sales Amount]) → Computes running total.

🚀 Result: The measure calculates a cumulative sales total while keeping the Date filter applied.

5️⃣ ALL vs. ALLEXCEPT: When to Use What?

6️⃣ Common Mistakes When Using ALL and ALLEXCEPT

🚨 Mistake 1: Using ALLEXCEPT Without Specifying Columns

DAX

CALCULATE(SUM(Sales[Sales Amount]), ALLEXCEPT(Sales))

❌ Error: This removes all filters, making it the same as using ALL(Sales).

✅ Correct Usage:

DAX

CALCULATE(SUM(Sales[Sales Amount]), ALLEXCEPT(Sales, Sales[Region]))

🚨 Mistake 2: Misusing ALL with Multiple Columns

DAX

CALCULATE(SUM(Sales[Sales Amount]), ALL(Sales[Region], Sales[Product Name]))

❌ Error: ALL doesn’t accept multiple columns like this.

✅ Correct Usage:

DAX

CALCULATE(SUM(Sales[Sales Amount]), ALL(Sales[Region]), ALL(Sales[Product Name]))

7️⃣ Best Practices for Using ALL and ALLEXCEPT

• ✅ Use ALL for total calculations when you want to ignore all filters.
• ✅ Use ALLEXCEPT when you need to keep specific filters.
• ✅ Always test your calculations with different filters to ensure accuracy.
• ✅ Combine ALL/ALLEXCEPT with CALCULATE for advanced calculations.

8️⃣ Conclusion: Mastering ALL and ALLEXCEPT in Power BI

• 🚀 ALL helps in total and percentage calculations by removing filters.
• 🚀 ALLEXCEPT lets you remove filters but keep specific ones active.
• 🚀 Both functions are powerful when used with CALCULATE and other DAX functions.

By mastering these functions, you can create dynamic, filter-controlled reports in Power BI! 🎯

9️⃣ What’s Next?

📌 In the next blog, you’ll learn:

• ✅ How to Use RANKX for Dynamic Rankings in Power BI
• ✅ How to Create Top-N Analysis Using DAX
• ✅ Best Practices for Ranking Data Efficiently

🔔 Stay tuned to Virvijay.com for more Power BI tutorials!

📩 For any queries, reach out to support@virvijay.com

💬 Got questions? Drop them in the comments!