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.