ALL function for removing filters in Power BI - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to remove filters from the 'Sales' table using the ALL function.

Power BI

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

Drag options to blanks, or click blank then click option'

AREMOVEFILTERS

BFILTER

CALL

DVALUES

Attempts:

3 left

💡 Hint

Common Mistakes

Using FILTER instead of ALL causes syntax errors.

Using VALUES does not remove filters but returns distinct values.

The ALL function removes all filters from the specified table or column, allowing calculation over the entire dataset.

2fill in blank

medium

Complete the code to calculate total sales ignoring filters on the 'Product' column.

Power BI

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

Drag options to blanks, or click blank then click option'

AProduct

BDate

CCustomer

DRegion

Attempts:

3 left

💡 Hint

Common Mistakes

Choosing a column unrelated to products.

Using a table name instead of a column name.

The ALL function removes filters from the specified column. Here, removing filters from Sales[Product] calculates total sales for all products.

3fill in blank

hard

Fix the error in the code to correctly remove filters from the 'Region' column.

Power BI

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

Drag options to blanks, or click blank then click option'

A[Region]

B(Region)

C.Region

D{Region}

Attempts:

3 left

💡 Hint

Common Mistakes

Using parentheses instead of square brackets.

Omitting brackets entirely.

In DAX, columns are referenced with square brackets inside the table name, like Sales[Region]. Using parentheses or other symbols causes syntax errors.

4fill in blank

hard

Fill both blanks to calculate total sales ignoring filters on both 'Product' and 'Region' columns.

Power BI

Total Sales All Prod & Region = CALCULATE(SUM(Sales[Amount]), ALL(Sales[[1]]), ALL(Sales[[2]]))

Drag options to blanks, or click blank then click option'

AProduct

BDate

CRegion

DCustomer

Attempts:

3 left

💡 Hint

Common Mistakes

Using unrelated columns like Date or Customer.

Swapping the order of columns incorrectly.

Using ALL(Sales[Product]) and ALL(Sales[Region]) removes filters from both columns, calculating total sales across all products and regions.

5fill in blank

hard

Fill all three blanks to calculate total sales ignoring filters on 'Product', 'Region', and 'Date' columns.

Power BI

Total Sales All Prod, Region & Date = CALCULATE(SUM(Sales[Amount]), ALL(Sales[[1]]), ALL(Sales[[2]]), ALL(Sales[[3]]))

Drag options to blanks, or click blank then click option'

ACustomer

BDate

CRegion

DProduct

Attempts:

3 left

💡 Hint

Common Mistakes

Including 'Customer' instead of 'Date'.

Mixing up the order of columns.

Removing filters from Product, Region, and Date columns calculates total sales across all these dimensions.

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