ALL function for removing filters in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ dax_lod_result

intermediate

Calculate total sales ignoring filters on Product Category

Given a Sales table with columns [SalesAmount] and [ProductCategory], what is the result of this measure when the report page filters ProductCategory to 'Bikes' only?

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

Assume total sales for all categories is 1000, and sales for 'Bikes' category is 300.

Power BI

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

A 1000

B 300

C 0

D Error: ALL function cannot be used here

💡 Hint

ALL removes filters on the specified column, so the measure ignores the page filter on ProductCategory.

The ALL function removes any filters on Sales[ProductCategory], so the measure sums sales across all categories, ignoring the page filter.

❓ visualization

intermediate

Visualizing effect of ALL on filter context

You have a report page filtered to Year = 2023. You create two measures:

SalesFiltered = SUM(Sales[SalesAmount])
SalesAllYears = CALCULATE(SUM(Sales[SalesAmount]), ALL(Sales[Year]))

Which visualization best shows the difference between these two measures?

A A bar chart with Year on axis and both measures as values, showing SalesFiltered only for 2023 and SalesAllYears for all years

B A pie chart showing SalesFiltered and SalesAllYears as slices for 2023 only

C A table with Year and SalesFiltered only, ignoring SalesAllYears

D A line chart with Month on axis and SalesFiltered measure only

💡 Hint

ALL removes the Year filter, so SalesAllYears shows total sales for all years regardless of page filter.

The bar chart with Year axis and both measures shows how SalesFiltered respects the Year filter (only 2023) while SalesAllYears ignores it and shows total sales for all years.

🧠 Conceptual

advanced

Understanding ALL with multiple columns

What happens when you use ALL on multiple columns like this?

CALCULATE(SUM(Sales[SalesAmount]), ALL(Sales[ProductCategory], Sales[Year]))

Choose the correct explanation.

A Removes filters on all columns in the Sales table, not just ProductCategory and Year.

B Removes filters only if both ProductCategory and Year are filtered together, otherwise keeps filters.

C Removes filters on both ProductCategory and Year columns, so the measure sums sales ignoring filters on these columns.

D Causes a syntax error because ALL accepts only one column.

💡 Hint

ALL can accept multiple columns to remove filters on all of them.

ALL(Sales[ProductCategory], Sales[Year]) removes filters on both columns, so the measure ignores filters on either column.

🔧 Formula Fix

advanced

Identify the error in this ALL usage

What error will this DAX measure produce?

Measure = CALCULATE(SUM(Sales[Amount]), ALL('Date'))

Assuming 'Date' is a table, not a column.

Power BI

Measure = CALCULATE(SUM(Sales[Amount]), ALL('Date'))

A Returns sum filtered only by Date table filters

B SyntaxError: ALL requires column references, not table names

C TypeError: Cannot use table name in ALL function

D No error, returns sum ignoring all filters on Date table

💡 Hint

ALL can accept a table name to remove all filters on that table.

ALL can accept a table name to remove all filters on that table, so this measure removes all filters on the Date table and sums Sales[Amount].

🎯 Scenario

expert

Using ALL to create a dynamic percentage of total

You want to create a measure that shows the percentage of total sales for each ProductCategory, ignoring any filters on ProductCategory but respecting other filters like Year.

Which DAX measure achieves this correctly?

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

B Percent of Total = DIVIDE(SUM(Sales[SalesAmount]), CALCULATE(SUM(Sales[SalesAmount]), ALL(Sales[ProductCategory])))

C Percent of Total = DIVIDE(SUM(Sales[SalesAmount]), CALCULATE(SUM(Sales[SalesAmount]), ALL(Sales[Year])))

D Percent of Total = DIVIDE(SUM(Sales[SalesAmount]), CALCULATE(SUM(Sales[SalesAmount]), ALLSELECTED(Sales[ProductCategory])))

💡 Hint

You want to remove filters only on ProductCategory to get total sales across all categories but keep other filters.

Using ALL(Sales[ProductCategory]) removes filters on ProductCategory only, so the denominator is total sales across all categories respecting other filters like Year.

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