ALL/ALLEXCEPT — Use ALL/ALLEXCEPT to control filter removal and context (intro) - Microsoft Power Bi

SECTION 1

Historical Context & Motivation

Before the advent of modern BI expression languages, analysts working with OLAP cubes and relational databases relied on SQL subqueries or MDX scope statements to override the current grouping context and compute totals that ignored certain dimensional slicers. This workflow was fragile—any change to the underlying schema could cascade into broken queries, and cross-table context manipulation required deep expertise in the query engine's internals. The Data Analysis Expressions (DAX) language was designed, in part, to provide a declarative, formula-based approach to precisely this problem: controlling which filters are active when a measure is evaluated.

2009 • PowerPivot & DAX Debut

Microsoft ships PowerPivot as an Excel add-in, introducing DAX. The language includes CALCULATE and table functions like ALL, establishing the filter-context paradigm that distinguishes DAX from SQL.

2012 • ALLEXCEPT Introduced

The ALLEXCEPT function is added to DAX, allowing analysts to clear all filters on a table except specified columns—dramatically simplifying ratio-to-parent and semi-additive calculations.

2015 • Power BI Desktop Launch

Power BI Desktop brings DAX to a broader audience. ALL and ALLEXCEPT become foundational for building robust measures in interactive dashboards where slicer selections dynamically alter filter context.

2018–Present • Mature Filter Context Patterns

Community best practices (such as SQLBI's CALCULATE pattern taxonomy) codify ALL/ALLEXCEPT as essential tools. Engine optimizations in the VertiPaq storage engine make these functions highly performant even on billion-row models.

The central question these functions address is deceptively simple: how do you compute an aggregate that ignores some—or all—of the filters currently applied to your data? In a dashboard, clicking a slicer for "Region = West" restricts every visual on the page. But what if you need a measure that always shows the grand total across all regions so you can display each region's share? The answer is ALL and ALLEXCEPT.

SECTION 2

Core Principles & Definitions

To understand ALL and ALLEXCEPT, you first need a firm grasp of filter context—the set of active filters that constrain which rows participate in a measure's evaluation. Every cell in a Power BI visual has a unique filter context formed by the intersection of row labels, column labels, slicer selections, page-level filters, and report-level filters. When DAX evaluates a measure, the engine first applies this filter context to the model's tables and then performs the aggregation. ALL and ALLEXCEPT are CALCULATE modifier functions—they are most commonly used as arguments inside CALCULATE to override (remove) parts of the current filter context before the aggregation runs.

Filter Context

The combination of all active filters (slicers, visual axes, page/report filters) that determines which rows a measure 'sees.' Think of it as a WHERE clause that the engine builds automatically from the visual layout.

ALL( Table | Column )

Returns the entire table or all distinct values of a column, ignoring any filters currently applied. When used inside CALCULATE, it removes filters from the specified scope, effectively restoring those dimensions to their unfiltered state.

ALLEXCEPT( Table, Col₁, Col₂, … )

Removes all filters on the specified table except for the listed columns. It is a shorthand for clearing many columns at once while explicitly preserving the filters you still need—useful for ratio-to-parent calculations.

CALCULATE( expression, filters… )

The engine function that evaluates an expression in a modified filter context. ALL and ALLEXCEPT appear as filter-modifier arguments inside CALCULATE, instructing the engine which filters to strip before evaluating the expression.

Context Transition

CALCULATE also triggers row-context-to-filter-context transition inside iterators. Understanding this transition is important because ALL/ALLEXCEPT modifiers interact with both the outer filter context and any transitioned context.

✦

KEY TAKEAWAY

Think of filter context like an access-control list on a database query: by default, every slicer and axis adds a restriction. ALL is the equivalent of temporarily revoking all those restrictions for a specific table or column, while ALLEXCEPT is a whitelist—it revokes everything except the columns you name. In software-engineering terms, ALL is a wildcard permission reset, whereas ALLEXCEPT is a scoped reset with explicit retain rules.

SECTION 3

Visual Explanation — Filter Context Flow

The diagram below illustrates how filter context flows through a CALCULATE expression when ALL or ALLEXCEPT modifiers are present. On the left, you see the original filter context produced by a visual's axes and slicers. The center column represents the modifier's action—either clearing all filters (ALL) or clearing selectively (ALLEXCEPT). On the right, the resulting modified context feeds into the aggregation function.

The left box shows the original filter context with four active filters. The ALL path (top) strips every filter, producing a grand total. The ALLEXCEPT path (bottom) strips everything except Year, producing a year-level subtotal. The pseudocode panel at the bottom shows the corresponding DAX syntax.

Notice that the key distinction is structural, not merely syntactic. When you write ALL(Sales), the engine returns the full, unfiltered Sales table as a virtual table, and CALCULATE uses that table to override any existing filters. When you write ALLEXCEPT(Sales, Sales[Year]), the engine returns the Sales table filtered only by Year—every other column's filter is removed. This means ALLEXCEPT is semantically equivalent to calling ALL on every column of the table except the ones you list. Understanding this equivalence is critical: ALLEXCEPT is not a fundamentally different operation, but a concise shorthand that prevents verbose multi-column ALL specifications.

SECTION 4

How ALL and ALLEXCEPT Work Under the Hood

From the perspective of the DAX engine, both ALL and ALLEXCEPT operate on the filter context stack—the layered set of filters that CALCULATE manages. Each call to CALCULATE creates a new evaluation context by copying the current filter context, applying modifier functions (like ALL/ALLEXCEPT), adding any new filter predicates, and then evaluating the inner expression. The modifier functions operate by producing virtual tables that CALCULATE uses to overwrite existing column filters via set intersection.

ALL SEMANTIC EQUIVALENT

CALCULATE( expr, ALL(T) ) ≡ CALCULATE( expr ) evaluated with no filters on table T

Where T is any table in the model. ALL(T) returns all rows of T, which CALCULATE uses to replace the current filter on T with a filter containing every row—effectively no filter.

ALLEXCEPT SEMANTIC EQUIVALENT

ALLEXCEPT(T, T[C₁], T[C₂]) ≡ ALL(T[C₃]), ALL(T[C₄]), … ALL(T[Cₙ]) for all Cᵢ ∉ {C₁, C₂}

ALLEXCEPT removes filters on every column of table T except C₁ and C₂. The retained columns keep their current filter context intact. This is logically identical to calling ALL on each non-retained column individually.

PERCENT-OF-TOTAL PATTERN

% of Total = DIVIDE( SUM(Sales[Amount]), CALCULATE( SUM(Sales[Amount]), ALL(Sales) ) )

The numerator computes the filtered sum (e.g., one region). The denominator uses ALL(Sales) to compute the grand total. DIVIDE handles division-by-zero gracefully. This is the canonical use case for ALL.

⚠

ALL on a Column vs. ALL on a Table

There is an important distinction between ALL(Sales) and ALL(Sales[Region]). The table form removes filters from every column simultaneously. The column form removes the filter only from the Region column, leaving Year, Category, and other column filters intact. This gives you fine-grained control: you can remove a single dimension's filter while preserving the rest of the context.

SECTION 5

Common Patterns & Classification

In practice, ALL and ALLEXCEPT appear in a handful of recurring patterns. Recognizing these patterns accelerates measure authoring and helps you choose the right function for each scenario. The diagram below categorizes the most common use cases and shows which function variant to apply.

Decision tree for choosing between ALL and ALLEXCEPT. Start at the top: if you need to remove all filters, use ALL(Table). If you need to keep some filters, decide whether it's simpler to list what to remove (use ALL(Column) for each) or what to keep (use ALLEXCEPT).

Pattern Name	DAX Syntax	Filters Removed	Typical Scenario
Grand Total	`CALCULATE( SUM(…), ALL(Sales) )`	All columns on Sales table	Denominator for percent-of-total KPIs
Remove Single Column	`CALCULATE( SUM(…), ALL(Sales[Region]) )`	Only the Region column	Show all-region total while year/category slicers stay active
Ratio to Parent	`CALCULATE( SUM(…), ALLEXCEPT(Sales, Sales[Category]) )`	All columns except Category	Category-level subtotal for computing each region's share within a category
Multiple Column Retain	`CALCULATE( SUM(…), ALLEXCEPT(Sales, Sales[Year], Sales[Category]) )`	All columns except Year and Category	Year-Category subtotal, ignoring region, color, etc.

Common ALL/ALLEXCEPT patterns and their typical use cases

SECTION 6

Worked Example — Percent of Category Total

Consider a Sales table with columns Region, Category, Year, and Amount. A user places Region on the row axis and Category on a slicer (set to "Bikes"). The goal is to create a measure that shows each region's share of the total Bikes sales, regardless of any other slicers that might be active.

Building a % of Category Measure

1
Step 1 — Define the Numerator

The numerator is simply the sum of Amount in the current filter context. Since Region is on the row axis and Category is filtered by the slicer, each cell already contains the correct region-level amount for Bikes.

Numerator = SUM( Sales[Amount] )
2
Step 2 — Define the Denominator Using ALLEXCEPT

The denominator should sum Amount across all regions but keep the Category filter (Bikes) active. We use ALLEXCEPT to remove every filter on Sales except Category. This ensures that if additional columns (Year, Color) are added to the visual later, only the Category filter survives.

Denominator = CALCULATE( SUM( Sales[Amount] ), ALLEXCEPT( Sales, Sales[Category] ) )
3
Step 3 — Combine with DIVIDE

We wrap the ratio in DIVIDE, which returns BLANK() instead of an error when the denominator is zero. This is a DAX best practice—never use the / operator when the denominator could be zero.

% of Category = DIVIDE( SUM( Sales[Amount] ), CALCULATE( SUM( Sales[Amount] ), ALLEXCEPT( Sales, Sales[Category] ) ) )
4
Step 4 — Verify Against Sample Data

Suppose Category = Bikes, and the Sales table has: West = $120K, East = $80K, North = $50K, South = $50K. The ALLEXCEPT denominator yields $300K (all regions, Bikes only). The West cell computes DIVIDE(120K, 300K) = 0.40, or 40%. East computes 80K / 300K ≈ 26.67%, and so on. The column sums to 100%, confirming correctness.

West: 40.00%, East: 26.67%, North: 16.67%, South: 16.67% — totals 100% ✓
5
Step 5 — Consider the ALL(Column) Alternative

An alternative approach uses ALL on just the Region column: CALCULATE( SUM(Sales[Amount]), ALL(Sales[Region]) ). This also removes the Region filter while keeping Category. However, it also preserves any Year or Color filters that might be active. If you want the denominator to ignore those too, ALLEXCEPT(Sales, Sales[Category]) is the correct choice because it explicitly defines what to keep rather than what to remove.

Use ALLEXCEPT when you want a durable denominator that only keeps specific dimensions.

SECTION 7

Strengths, Limitations & Comparisons

While ALL and ALLEXCEPT are powerful, they have distinct strengths and limitations that you should weigh when designing measures. The following table compares the two functions across several dimensions, then contrasts them with related functions like REMOVEFILTERS and VALUES.

Dimension	ALL	ALLEXCEPT
Scope of removal	Removes all filters on the specified table or column(s). Can target a single column via ALL(Table[Col]).	Removes all filters on a table except named columns. Cannot target a single column alone—always takes a table as the first argument.
Readability	Clear when removing one or two columns. Becomes verbose when you need to keep many columns filtered and remove the rest.	Concise when you want to keep a few columns and remove the rest. Intention is clear from the function name.
Schema resilience	Adding a new column to the table does NOT affect the measure—ALL(Table) always removes everything. ALL(Column) is also unaffected by new columns.	Adding a new column means ALLEXCEPT automatically removes filters on it too (since it's not in the retain list). This can be a feature or a subtle bug.
Performance	Highly optimized—the engine recognizes ALL as a complete filter clear. Minimal overhead.	Also well-optimized. In modern VertiPaq, ALLEXCEPT is rewritten internally to an equivalent ALL-column plan, so performance is comparable.
Cross-table filters	ALL on a dimension table removes filters that propagate through relationships. Use carefully to avoid unintended cascading filter removal.	Same caveat applies. Retaining a column on a dimension table still allows cross-filter propagation on that column.

Side-by-side comparison of ALL vs. ALLEXCEPT

🔑

WHEN TO USE WHICH

A useful heuristic: if the number of columns you want to remove is smaller than the number you want to keep, use ALL(Column) for each. If the number you want to keep is smaller, use ALLEXCEPT. Think of it like .gitignore rules: ALL(Column) is an explicit ignore list, while ALLEXCEPT is a blanket ignore with a whitelist.

SECTION 8

Connection to Advanced Filter Manipulation

ALL and ALLEXCEPT are introductory tools in a broader family of DAX filter-manipulation functions. As your models grow in complexity—with role-playing dimensions, many-to-many relationships, and calculation groups—you will encounter scenarios where these basic functions are insufficient. Understanding how ALL/ALLEXCEPT connect to these advanced techniques provides a roadmap for continued learning.

Feature	ALL / ALLEXCEPT (This Lesson)	Advanced Alternatives
Filter removal	Removes filters from one table or specific columns. Operates at a single-table level.	REMOVEFILTERS (alias for ALL in filter args). ALLSELECTED removes only user-interactive filters, preserving query-level filters from visual subtotals.
Cross-table context	ALL on a dimension table clears filters that flow through relationships, but control is coarse.	CROSSFILTER modifies relationship behavior dynamically. TREATAS injects virtual relationships without physical model changes.
Iterative context	Works inside CALCULATE to modify outer filter context. Does not directly interact with row context.	SELECTEDVALUE and HASONEVALUE inspect the current filter context. EARLIER (deprecated) accessed outer row context in nested iterators.
Semi-additive measures	ALL on Date table can remove time filters for snapshot-style aggregations (e.g., inventory).	LASTDATE, LASTNONBLANK combined with CALCULATE provide precise semi-additive logic (last balance per period).

Progression from ALL/ALLEXCEPT to advanced filter techniques

💡

REMOVEFILTERS vs. ALL

Starting in 2019, Microsoft introduced REMOVEFILTERS as a syntactic alias for ALL when used inside CALCULATE. Functionally, CALCULATE(expr, REMOVEFILTERS(Sales)) is identical to CALCULATE(expr, ALL(Sales)). The new name was introduced purely for readability—REMOVEFILTERS makes the intent explicit when you're modifying context rather than returning a table. Outside CALCULATE, ALL still returns a table (useful in iterator functions), while REMOVEFILTERS is valid only as a CALCULATE modifier.

SECTION 9

Practice Problems

Question 1: CONCEPTUAL

Explain, in your own words, the difference between ALL(Sales) and ALL(Sales[Region]) when used inside CALCULATE. Under what circumstances would they produce different results?

Show answer

ALL(Sales) removes filters from every column of the Sales table—Region, Category, Year, Amount, and any other column. ALL(Sales[Region]) removes the filter only from the Region column, leaving all other column filters intact. They produce different results whenever the filter context includes filters on columns other than Region. For example, if a slicer sets Category = 'Bikes' and a visual row sets Region = 'West', ALL(Sales) inside CALCULATE would ignore both filters and sum across all categories and regions, while ALL(Sales[Region]) would ignore only the Region filter, computing a total across all regions but still restricted to Bikes.

Question 2: BASIC CALCULATION

Given a Sales table with Region ∈ {North, South, East, West} and amounts {$50K, $30K, $80K, $40K} respectively, write a DAX measure called PctTotal that computes each region's percentage of the grand total. Then compute the value of PctTotal in the filter context where Region = 'East'.

Show answer

The measure is: PctTotal = DIVIDE( SUM(Sales[Amount]), CALCULATE( SUM(Sales[Amount]), ALL(Sales) ) ). The grand total across all regions is $50K + $30K + $80K + $40K = $200K. In the East context, the numerator is $80K. The denominator uses ALL(Sales) to remove all filters, yielding $200K. Thus PctTotal = DIVIDE(80000, 200000) = 0.40, or 40%. This pattern is the canonical percent-of-total calculation using ALL.

Question 3: INTERMEDIATE

A Power BI matrix visual has Category on rows and Year on columns. You need a measure PctOfYearTotal that shows each category's share within the year (i.e., each column sums to 100%). Write the measure using ALLEXCEPT and explain why ALLEXCEPT is preferable to ALL(Sales[Category]) in this scenario.

Show answer

PctOfYearTotal = DIVIDE( SUM(Sales[Amount]), CALCULATE( SUM(Sales[Amount]), ALLEXCEPT(Sales, Sales[Year]) ) ). ALLEXCEPT(Sales, Sales[Year]) removes filters from every column except Year, so the denominator computes the year-level total regardless of Category. Using ALL(Sales[Category]) would also remove the Category filter, but it would preserve any other filters (e.g., Region slicers). If you add a Region slicer later, the ALL(Category) version would keep that filter in the denominator, which might not be desired. ALLEXCEPT explicitly states: 'only Year matters for the denominator,' making the measure more robust against future schema or slicer changes.

Question 4: APPLIED

You are building an inventory dashboard. The Inventory table has columns: WarehouseID, ProductID, Date, and QuantityOnHand. Inventory is a snapshot—each row records the quantity at a specific warehouse on a specific date. You need a measure TotalInventory that always shows the sum of the most recent date's quantity across all warehouses, even when the user selects specific warehouses via a slicer. Write the measure and explain where ALL is needed.

Show answer

TotalInventory = CALCULATE( SUM(Inventory[QuantityOnHand]), LASTDATE(Inventory[Date]), ALL(Inventory[WarehouseID]) ). LASTDATE(Inventory[Date]) restricts the Date filter to the latest date in the current context. ALL(Inventory[WarehouseID]) removes any warehouse slicer filter, so the sum includes all warehouses. Without ALL(WarehouseID), selecting 'Warehouse A' would show only that warehouse's inventory. Note that this approach preserves any date filters from time slicers (LASTDATE narrows within the existing date context), while ALL(WarehouseID) specifically removes only the warehouse filter. If you wanted to also ignore date slicers, you would use ALL(Inventory) instead, then add LASTDATE to set the date to the absolute latest.

Question 5: CRITICAL THINKING

Consider the following two measures: MeasureA = CALCULATE( SUM(Sales[Amount]), ALLEXCEPT(Sales, Sales[Year], Sales[Category]) ) MeasureB = CALCULATE( SUM(Sales[Amount]), ALL(Sales[Region]), ALL(Sales[Color]), ALL(Sales[CustomerID]) ) Assume Sales has exactly five columns: Year, Category, Region, Color, and CustomerID (plus Amount). Are MeasureA and MeasureB semantically equivalent? What happens if a sixth column, Sales[Channel], is later added to the model? Discuss the implications for measure maintenance and defensive DAX coding.

Show answer

With exactly five non-measure columns, MeasureA and MeasureB are semantically equivalent. ALLEXCEPT(Sales, Sales[Year], Sales[Category]) removes filters on Region, Color, and CustomerID—exactly what MeasureB does by explicitly listing ALL for each. However, if a sixth column Sales[Channel] is added, the behaviors diverge. MeasureA (ALLEXCEPT) automatically removes the filter on Channel as well, because Channel is not in the retain list. MeasureB does not mention Channel, so it preserves any filter on Channel. This has real-world implications: ALLEXCEPT is schema-resilient in the sense that it adapts to new columns, but this can introduce subtle bugs if the new column carries unexpected filters. MeasureB is explicit but requires manual updating when the schema changes. Defensive DAX coding recommends documenting your intent: use ALLEXCEPT when you definitively want only specific columns to remain filtered, and accept that new columns will be cleared. Use explicit ALL(Column) calls when you want strict, auditable control over exactly which columns lose their filters.

SUMMARY

Summary

The ALL and ALLEXCEPT functions are DAX's primary tools for removing filters from the filter context during measure evaluation. ALL(Table) strips every filter from the specified table, enabling grand total and percent-of-total calculations. ALL(Column) offers finer granularity by removing the filter from a single column. ALLEXCEPT inverts the logic—it clears all filters except on specified columns, making it ideal for ratio-to-parent patterns where a subtotal must respect certain dimensions.

Both functions are used as CALCULATE modifier arguments, overriding the filter context before the inner expression evaluates. A practical heuristic: use ALL when the set of filters to remove is small, and ALLEXCEPT when the set to retain is small. Be aware of schema evolution: ALLEXCEPT automatically clears filters on newly added columns, which can be both a strength and a source of subtle bugs. Mastering these two functions unlocks the ability to write robust, context-aware measures that power dynamic dashboards in Power BI.