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.