ALL function for removing filters in Power BI - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants to see the total sales amount for each product category, ignoring any filters applied on the product subcategory.

📊 Data: You have sales data with columns: Date, Product Category, Product Subcategory, Sales Amount.

🎯 Deliverable: Create a report showing total sales by product category that ignores filters on product subcategory.

Sample Data

Date	Product Category	Product Subcategory	Sales Amount
2024-01-01	Electronics	Phones	500
2024-01-02	Electronics	Computers	700
2024-01-03	Furniture	Chairs	300
2024-01-04	Furniture	Tables	400
2024-01-05	Clothing	Shirts	200
2024-01-06	Clothing	Pants	250
2024-01-07	Electronics	Phones	600
2024-01-08	Furniture	Chairs	350

1

Step 1: Create a new measure to calculate total sales ignoring filters on Product Subcategory.

Total Sales by Category = CALCULATE(SUM('Sales'[Sales Amount]), ALL('Sales'[Product Subcategory]))

Expected Result

This measure sums Sales Amount for each Product Category ignoring any filter on Product Subcategory.

2

Step 2: Create a table visual in Power BI with Product Category in rows and the new measure 'Total Sales by Category' as values.

Table visual configuration: Rows = 'Sales'[Product Category], Values = [Total Sales by Category]

Expected Result

The table shows total sales per product category ignoring any subcategory filters.

3

Step 3: Apply a slicer or filter on Product Subcategory to test that the total sales by category does not change.

Add slicer on 'Sales'[Product Subcategory] and select any subcategory.

Expected Result

The total sales by category remains the same regardless of the subcategory selected.

Final Result

Product Category | Total Sales by Category
-----------------|------------------------
Electronics      | 1800
Furniture        | 1050
Clothing         | 450

✓Total sales by product category sums all subcategories even when a subcategory filter is applied.

✓The ALL function removes filters on Product Subcategory, allowing total sales to be consistent.

✓This helps managers see overall category performance without subcategory filter impact.

Bonus Challenge

Modify the measure to ignore filters on both Product Subcategory and Date columns.

Show Hint

Use ALL with multiple columns: ALL('Sales'[Product Subcategory], 'Sales'[Date]) inside CALCULATE.

Practice

1. What does the ALL function do in Power BI DAX?

easy

A. It removes filters from a table or column, showing all data.

B. It applies additional filters to narrow down data.

C. It sorts data in ascending order.

D. It creates a new calculated column.

Solution

1. Step 1: Understand the purpose of ALL

   The ALL function removes any filters applied on a table or column, so the calculation sees all rows regardless of slicers or filters.

2. Step 2: Compare with other options

   Options B, C, and D describe different functions: filtering, sorting, and creating columns, which ALL does not do.

3. Final Answer:

   It removes filters from a table or column, showing all data. -> Option A

4. Quick Check:

   ALL removes filters = A [OK]

Hint: ALL clears filters to show full data set [OK]

Common Mistakes:

• Thinking ALL applies filters instead of removing them
• Confusing ALL with sorting or column creation
• Assuming ALL changes data values

2. Which of the following is the correct syntax to remove filters from the 'Sales' table using ALL inside CALCULATE?

easy

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

B. CALCULATE(SUM(Sales[Amount]), REMOVEFILTERS(Sales))

C. CALCULATE(SUM(Sales[Amount]), FILTER(ALL(Sales)))

D. CALCULATE(SUM(Sales[Amount]), ALLFILTERS(Sales))

Solution

1. Step 1: Identify correct ALL usage

   The ALL function is used inside CALCULATE as ALL(TableName) to remove filters from that table.

2. Step 2: Check other options for syntax errors

   CALCULATE(SUM(Sales[Amount]), REMOVEFILTERS(Sales)) uses REMOVEFILTERS which is valid but not ALL; CALCULATE(SUM(Sales[Amount]), FILTER(ALL(Sales))) misuses FILTER with ALL; CALCULATE(SUM(Sales[Amount]), ALLFILTERS(Sales)) uses ALLFILTERS which is different from ALL.

3. Final Answer:

   CALCULATE(SUM(Sales[Amount]), ALL(Sales)) -> Option A

4. Quick Check:

   ALL inside CALCULATE = A [OK]

Hint: Use ALL(Table) inside CALCULATE to remove filters [OK]

Common Mistakes:

• Confusing ALL with REMOVEFILTERS or ALLFILTERS
• Incorrect use of FILTER with ALL
• Syntax errors like missing parentheses

3. Given the measure:

 Total Sales All = CALCULATE(SUM(Sales[Amount]), ALL(Sales)) 

If a report page filter limits Sales[Region] to 'West', what will Total Sales All show?

medium

A. Sum of Sales Amount only for 'West' region

B. Zero because filter removes all data

C. Sum of Sales Amount for all regions ignoring the filter

D. Error due to conflicting filters

Solution

1. Step 1: Understand ALL effect on filters

   ALL(Sales) removes all filters on the Sales table, including the Region filter.

2. Step 2: Calculate measure ignoring page filter

   CALCULATE with ALL returns the sum of Sales Amount for all regions, ignoring the 'West' filter.

3. Final Answer:

   Sum of Sales Amount for all regions ignoring the filter -> Option C

4. Quick Check:

   ALL removes filters, so total ignores 'West' filter [OK]

Hint: ALL ignores filters, shows total regardless of slicers [OK]

Common Mistakes:

• Assuming ALL respects page filters
• Thinking ALL causes errors with filters
• Confusing ALL with partial filter removal

4. You wrote this measure:

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

But it still applies other filters on the Sales table. What is the likely issue?

medium

A. The measure syntax is invalid and causes errors.

B. ALL cannot be used on columns, only on tables.

C. You need to use REMOVEFILTERS instead of ALL.

D. ALL(Sales[Region]) removes filters only on Region column, not the whole table.

Solution

1. Step 1: Understand ALL on column vs table

   ALL(Sales[Region]) removes filters only on the Region column, so other filters on Sales table remain.

2. Step 2: Explain why other filters might persist

   If other filters exist on Sales table or related tables, they still apply; only Region column filters are removed.

3. Final Answer:

   ALL(Sales[Region]) removes filters only on Region column, not the whole table. -> Option D

4. Quick Check:

   ALL on column removes only that column's filters [OK]

Hint: ALL(column) clears that column's filters only [OK]

Common Mistakes:

• Thinking ALL(column) removes all table filters
• Believing ALL is invalid on columns
• Confusing ALL with REMOVEFILTERS

5. You want to create a measure that calculates the percentage of total sales ignoring any filters on the 'Product' table but respecting other filters. Which DAX formula correctly uses ALL to achieve this?

hard

A. Percent of Total = DIVIDE(SUM(Sales[Amount]), CALCULATE(SUM(Sales[Amount]), ALL(Sales)))

B. Percent of Total = DIVIDE(SUM(Sales[Amount]), CALCULATE(SUM(Sales[Amount]), ALL(Product)))

C. Percent of Total = DIVIDE(SUM(Sales[Amount]), CALCULATE(SUM(Sales[Amount]), ALL(Product[Category])))

D. Percent of Total = DIVIDE(SUM(Sales[Amount]), CALCULATE(SUM(Sales[Amount]), REMOVEFILTERS(Product)))

Solution

1. Step 1: Identify the table to remove filters from

   We want to ignore filters on the entire Product table, so use ALL(Product) inside CALCULATE.

2. Step 2: Confirm measure calculates percentage correctly

   DIVIDE numerator by total ignoring Product filters gives correct percent respecting other filters.

3. Step 3: Check other options

   Percent of Total = DIVIDE(SUM(Sales[Amount]), CALCULATE(SUM(Sales[Amount]), ALL(Sales))) removes filters on Sales, not Product; Percent of Total = DIVIDE(SUM(Sales[Amount]), CALCULATE(SUM(Sales[Amount]), ALL(Product[Category]))) removes filters only on Product[Category]; Percent of Total = DIVIDE(SUM(Sales[Amount]), CALCULATE(SUM(Sales[Amount]), REMOVEFILTERS(Product))) uses REMOVEFILTERS which works but question asks for ALL usage.

4. Final Answer:

   Percent of Total = DIVIDE(SUM(Sales[Amount]), CALCULATE(SUM(Sales[Amount]), ALL(Product))) -> Option B

5. Quick Check:

   ALL(Product) removes Product filters for total [OK]

Hint: Use ALL(Product) inside CALCULATE for total ignoring Product filters [OK]

Common Mistakes:

• Removing filters on wrong table
• Using ALL on column instead of table
• Confusing REMOVEFILTERS with ALL