ALL/ALLEXCEPT — Use ALL/ALLEXCEPT to control filter removal...

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).

Common ALL/ALLEXCEPT patterns and their typical use cases

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.

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

Step 1 — Define the NumeratorThe 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] )

Step 2 — Define the Denominator Using ALLEXCEPTThe 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] ) )

Step 3 — Combine with DIVIDEWe 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] ) ) )

Step 4 — Verify Against Sample DataSuppose 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% ✓

Step 5 — Consider the ALL(Column) AlternativeAn 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.

Side-by-side comparison of ALL vs. ALLEXCEPT

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.

🔑 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.

Progression from ALL/ALLEXCEPT to advanced filter techniques

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).

💡 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

PROBLEM 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?

PROBLEM 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'.

PROBLEM 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.

PROBLEM 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.

PROBLEM 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.

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.