ALL function for removing filters in Power BI - Deep Dive

Overview - ALL function for removing filters

What is it?

The ALL function in Power BI is a tool that removes filters from one or more columns or entire tables in your data model. It helps you see the full data without any restrictions applied by slicers or other filters. This is useful when you want to calculate totals or compare filtered data against the whole dataset. It works inside DAX formulas to control how data is filtered during calculations.

Why it matters

Without the ability to remove filters, your reports would only show data limited by current selections, making it hard to calculate overall totals or percentages correctly. The ALL function solves this by letting you ignore filters temporarily, so you can compare parts of your data to the whole. This makes your reports more accurate and insightful, helping businesses make better decisions.

Where it fits

Before learning ALL, you should understand basic filtering and how slicers affect visuals in Power BI. After mastering ALL, you can explore related functions like ALLEXCEPT and REMOVEFILTERS, and learn advanced DAX techniques for dynamic calculations and context manipulation.

Mental Model

Core Idea

The ALL function clears away filters on specified data so calculations see the entire dataset, not just the filtered part.

Think of it like...

Imagine looking through a window with colored glass that only shows certain colors (filters). Using ALL is like removing the colored glass so you see all colors clearly, not just the ones the glass lets through.

┌───────────────┐
│ Filtered Data │
│ (with slicers)│
└──────┬────────┘
       │ ALL removes filters
       ▼
┌───────────────────┐
│ Full Data Set View │
│ (no filters)       │
└───────────────────┘

Build-Up - 6 Steps

1

Foundation

Understanding Filters in Power BI

Concept: Learn what filters do in Power BI and how they limit data shown in visuals.

Filters in Power BI act like lenses that only let certain data through. For example, if you select a year in a slicer, only data from that year appears in your charts. Filters can come from slicers, page filters, or visual-level filters.

Result

You see only a subset of your data based on the filters applied.

Understanding filters is key because ALL works by removing these filters to show all data.

2

Foundation

What Does ALL Function Do?

Concept: Introduce the ALL function as a way to ignore filters on columns or tables.

The ALL function takes a column or table as input and returns all rows from it, ignoring any filters applied. For example, ALL('Sales') returns all sales data, even if a slicer filters by year.

Result

Calculations using ALL see the full data, not just filtered parts.

Knowing that ALL removes filters helps you control what data your formulas use.

3

Intermediate

Using ALL in Calculated Measures

🤔Before reading on: Do you think ALL changes the data shown in visuals or just the calculation context? Commit to your answer.

Concept: Learn how to use ALL inside measures to calculate totals ignoring filters.

Example measure: Total Sales All = CALCULATE(SUM('Sales'[Amount]), ALL('Sales')) This measure sums all sales ignoring any filters on the Sales table. If a slicer filters by year, this measure still shows total sales for all years.

Result

You get a total sales number that does not change with slicers or filters.

Understanding that ALL changes calculation context, not visual filters, lets you create dynamic comparisons.

4

Intermediate

ALL with Specific Columns vs Entire Tables

🤔Before reading on: Does ALL('Table') remove filters on all columns or just one? Commit to your answer.

Concept: Distinguish between removing filters on specific columns or whole tables.

ALL can be used on one column: ALL('Sales'[Year]) removes filters only on Year. Or on a whole table: ALL('Sales') removes filters on all columns in Sales. This affects how much filtering is ignored in calculations.

Result

You control filter removal precisely, affecting calculation results accordingly.

Knowing the difference helps you avoid removing too many or too few filters.

5

Advanced

Combining ALL with CALCULATE for Dynamic Analysis

🤔Before reading on: Will using ALL inside CALCULATE always return the same result regardless of filters? Commit to your answer.

Concept: Use ALL inside CALCULATE to override filters selectively for complex calculations.

CALCULATE changes filter context. Using ALL inside it can remove filters on some columns but keep others. Example: Sales vs Total = DIVIDE(SUM('Sales'[Amount]), CALCULATE(SUM('Sales'[Amount]), ALL('Sales'))) This calculates the percentage of filtered sales compared to total sales.

Result

You get dynamic percentages that adjust with some filters but compare to full totals.

Understanding how ALL works inside CALCULATE unlocks powerful dynamic reporting.

6

Expert

ALL vs REMOVEFILTERS: Subtle Differences

🤔Before reading on: Do ALL and REMOVEFILTERS behave exactly the same in all cases? Commit to your answer.

Concept: Explore the nuanced differences between ALL and REMOVEFILTERS functions.

Both remove filters but differ in behavior with relationships and filter propagation. REMOVEFILTERS is newer and can be more efficient in some cases. Example: REMOVEFILTERS('Sales') removes filters but respects some filter contexts differently than ALL. Experts choose based on performance and context needs.

Result

You understand when to use ALL or REMOVEFILTERS for best results.

Knowing these subtle differences helps optimize complex models and avoid unexpected results.

Under the Hood

ALL works by modifying the filter context that DAX uses during calculation. When you use ALL on a column or table, it tells the engine to ignore any filters applied to those columns or tables in the current context. Internally, this resets the filter context for those columns, so calculations see the full data. This does not remove filters from visuals but changes how measures calculate their results.

Why designed this way?

Power BI needed a way to control filter context precisely because reports often show filtered views but require totals or comparisons to full data. ALL was designed to give formula writers a simple way to override filters without changing the report layout. Alternatives like manually removing filters would be complex and error-prone, so ALL provides a clean, reusable abstraction.

┌───────────────┐
│ User Applies   │
│ Filters (e.g., │
│ slicers)       │
└──────┬────────┘
       │ Filter context includes filters
       ▼
┌───────────────────────────┐
│ DAX Calculation Engine     │
│                           │
│  ┌─────────────────────┐  │
│  │ ALL removes filters  │  │
│  │ on specified columns │  │
│  └─────────┬───────────┘  │
│            │ Filter context modified
│            ▼               │
│  Calculation uses full data│
└───────────────────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does ALL remove filters from visuals or just from calculations? Commit to yes or no.

Common Belief: ALL removes filters from the report visuals themselves.

Reality: ALL only removes filters inside DAX calculations; visuals still show filtered data.

Why it matters: Confusing this leads to expecting visuals to change when they won't, causing frustration and wrong report design.

Quick: Does ALL always remove filters on all columns when used on a table? Commit to yes or no.

Common Belief: Using ALL on a table removes filters only on the columns you want.

Reality: ALL on a table removes filters on all columns of that table, which might be more than intended.

Why it matters: This can cause calculations to ignore important filters accidentally, leading to incorrect results.

Quick: Are ALL and REMOVEFILTERS exactly the same? Commit to yes or no.

Common Belief: ALL and REMOVEFILTERS are interchangeable and behave identically.

Reality: They behave similarly but have subtle differences in filter propagation and performance.

Why it matters: Using the wrong one can cause unexpected calculation behavior or slower reports.

Quick: Does ALL ignore filters from related tables automatically? Commit to yes or no.

Common Belief: ALL removes filters from related tables through relationships automatically.

Reality: ALL only removes filters on the specified columns or tables, not on related tables unless explicitly specified.

Why it matters: Assuming it removes related filters can cause wrong totals or percentages.

Expert Zone

1

ALL removes filters but does not reset row context; understanding this distinction is crucial for complex iterators.

2

Using ALL inside CALCULATE can override filters selectively, but stacking multiple ALL calls can lead to unexpected context interactions.

3

Performance-wise, REMOVEFILTERS can be more efficient than ALL in large models, but ALL is more widely supported and understood.

When NOT to use

Avoid using ALL when you want to keep some filters intact; instead, use ALLEXCEPT to remove filters except on specified columns. Also, for clearing filters in newer models, REMOVEFILTERS might be preferable for performance. Don't use ALL to try to change visual filters; use slicers or page filters instead.

Production Patterns

In production, ALL is often used to calculate grand totals, percentages of total, or to reset filters for comparison metrics. Experts combine ALL with CALCULATE and other functions to build dynamic KPIs that respond correctly to user selections while showing overall context.

Connections

Context Transition in DAX

ALL modifies filter context, which is a key part of context transition.

Understanding ALL helps grasp how DAX switches between row and filter context, essential for writing correct measures.

SQL WHERE Clause

ALL is like ignoring the WHERE clause filters temporarily in SQL queries.

Knowing this analogy helps understand how ALL changes which rows are considered during calculations.

Optics and Light Filtering

ALL is conceptually similar to removing a colored filter from a lens to see the full light spectrum.

This cross-domain insight shows how removing filters reveals the complete picture, whether in data or light.

Common Pitfalls

#1 Removing filters from visuals instead of calculations

Wrong approach:

Measure = ALL('Sales')
// expecting this to clear slicers on report visuals

Correct approach:

Measure = CALCULATE(SUM('Sales'[Amount]), ALL('Sales'))
// removes filters only inside calculation, visuals remain filtered

Root cause:

Misunderstanding that ALL affects visuals rather than calculation filter context.

#2 Using ALL on a table when only one column's filter should be removed

Wrong approach:

Measure = CALCULATE(SUM('Sales'[Amount]), ALL('Sales'))
// removes all filters on Sales table unintentionally

Correct approach:

Measure = CALCULATE(SUM('Sales'[Amount]), ALL('Sales'[Year]))
// removes filter only on Year column

Root cause:

Not knowing ALL on table removes filters on all columns, causing over-removal.

#3 Confusing ALL with REMOVEFILTERS and expecting identical behavior

Wrong approach:

Measure = CALCULATE(SUM('Sales'[Amount]), REMOVEFILTERS('Sales'))
// assuming same as ALL in all cases

Correct approach:

Measure = CALCULATE(SUM('Sales'[Amount]), ALL('Sales'))
// choose based on context and performance needs

Root cause:

Lack of awareness of subtle differences between these functions.

Key Takeaways

The ALL function removes filters inside DAX calculations, letting you see the full dataset regardless of slicers or filters.

ALL changes the filter context for calculations but does not affect what data visuals display on reports.

Using ALL on a table removes filters on all its columns, while using it on specific columns removes filters only on those columns.

ALL combined with CALCULATE enables powerful dynamic measures like totals and percentages that compare filtered data to overall data.

Understanding the subtle differences between ALL and similar functions like REMOVEFILTERS helps optimize report accuracy and performance.

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