ALL function for removing filters in Power BI - Cell-by-Cell Formula Trace

Sample Data

Sales data for products with their categories and sales amounts.

Cell	Value
A1	Product
B1	Category
C1	Sales
A2	Apple
B2	Fruit
C2	100
A3	Banana
B3	Fruit
C3	150
A4	Carrot
B4	Vegetable
C4	200
A5	Broccoli
B5	Vegetable
C5	120

Formula Trace

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

Step 1: SUM(Sales[Sales])

Step 2: ALL(Sales[Category])

Step 3: CALCULATE(SUM(Sales[Sales]), ALL(Sales[Category]))

Cell Reference Map

    A         B          C
1 Product   Category   Sales
2 Apple     Fruit      100
3 Banana    Fruit      150
4 Carrot    Vegetable  200
5 Broccoli  Vegetable  120

Filters removed on column B (Category)

The formula removes filters on the Category column (B) to sum all sales regardless of category.

Result

    D
1 Total Sales All Categories
2 570

The measure shows total sales 570, summing all products ignoring any category filter.

Sheet Trace Quiz - 3 Questions

What does the ALL function do in this formula?

A Removes filters on the Category column

B Filters only Fruit category

C Sums sales only for filtered categories

D Adds a new filter on Category

Key Result

ALL removes filters on specified columns to calculate over entire data ignoring those filters.

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