Power BI ALLEXCEPT Function Guide

The ALLEXCEPT function is one of the most powerful filter-modification functions in the DAX language. It removes all filters from a table except for the columns you explicitly specify, making it essential for calculating percentage-of-parent totals, running aggregations, and building dynamic measures that respond correctly to slicer interactions in Power BI reports.

ALLEXCEPT Syntax and Parameters

The ALLEXCEPT function accepts a table as its first argument followed by one or more columns whose filters should be preserved. All other filters on the specified table are removed from the evaluation context.

ALLEXCEPT ( <Table>, <Column1> [, <Column2> [, ... ]] )

-- Example: Calculate total sales while preserving only the Region filter
TotalSalesByRegion =
CALCULATE (
    SUM ( Sales[Amount] ),
    ALLEXCEPT ( Sales, Sales[Region] )
)

• Table – The table from which all filters will be removed (except specified columns). Must be a physical table, not an expression.
• Column1, Column2, ... – One or more columns whose existing filters should be preserved during the CALCULATE evaluation
• Return value – A table with all filters removed except those on the specified columns. Typically used inside CALCULATE or CALCULATETABLE.

Calculating Percentage of Parent Total

One of the most common use cases for ALLEXCEPT is calculating what percentage each row contributes to its parent group. For example, showing each product's share of its category's total sales in a matrix visual.

% of Category Sales =
DIVIDE (
    SUM ( Sales[Amount] ),
    CALCULATE (
        SUM ( Sales[Amount] ),
        ALLEXCEPT ( Sales, Sales[Category] )
    )
)

-- In a matrix with Category and Product on rows:
-- Category A: 100% (total)
--   Product X: 45% (of Category A)
--   Product Y: 55% (of Category A)

• The numerator calculates sales at the current filter context (product level)
• The denominator uses ALLEXCEPT to remove all filters except Category, giving the category-level total
• DIVIDE handles division-by-zero gracefully, returning BLANK() instead of an error
• This pattern works with any aggregation function: SUM, COUNT, AVERAGE, DISTINCTCOUNT

ALLEXCEPT vs ALL vs REMOVEFILTERS

Understanding when to use ALLEXCEPT versus ALL or REMOVEFILTERS is critical for writing correct DAX measures. Each function manipulates filter context differently, and choosing the wrong one leads to incorrect calculations.

• ALL(Table) – Removes all filters from the entire table, including any column filters from slicers. Returns the full table without any row context.
• ALL(Column) – Removes filters from a specific column only. Other column filters remain active.
• ALLEXCEPT(Table, Column) – Removes all table filters except the specified columns. More concise than listing multiple ALL(Column) calls for complex models.
• REMOVEFILTERS() – The modern alias for ALL() when used as a CALCULATE modifier. Microsoft recommends REMOVEFILTERS for clarity when the intent is filter removal rather than table iteration.

-- These two measures are equivalent:
Measure_V1 =
CALCULATE (
    SUM ( Sales[Amount] ),
    ALLEXCEPT ( Sales, Sales[Region], Sales[Year] )
)

Measure_V2 =
CALCULATE (
    SUM ( Sales[Amount] ),
    ALL ( Sales[Product] ),
    ALL ( Sales[Customer] ),
    ALL ( Sales[Channel] )
)
-- ALLEXCEPT is more maintainable when the table has many columns

Advanced ALLEXCEPT Patterns for Enterprise Reports

In enterprise Power BI deployments, ALLEXCEPT enables sophisticated calculations that respond dynamically to user interactions. These patterns are commonly used in financial reporting, sales analytics, and operational dashboards.

-- Running total that resets by category
Running Total by Category =
CALCULATE (
    SUM ( Sales[Amount] ),
    FILTER (
        ALLEXCEPT ( Sales, Sales[Category] ),
        Sales[Date] <= MAX ( Sales[Date] )
    )
)

-- Rank within group
Product Rank in Category =
COUNTROWS (
    FILTER (
        ALLEXCEPT ( Sales, Sales[Category] ),
        CALCULATE ( SUM ( Sales[Amount] ) ) >=
        CALCULATE ( SUM ( Sales[Amount] ), ALLEXCEPT ( Sales, Sales[Category], Sales[Product] ) )
    )
)

• Running totals by group – Use ALLEXCEPT with FILTER to calculate cumulative sums that reset at each category boundary
• Ranking within groups – Combine ALLEXCEPT with COUNTROWS and FILTER to rank items within their parent category
• Period-over-period by segment – Preserve segment filters while removing date filters to calculate prior-period comparisons within each segment
• Dynamic benchmarks – Create measures that compare individual performance against group averages while respecting slicer selections

Performance Considerations and Best Practices

ALLEXCEPT can impact query performance in large models, especially when used with high-cardinality tables. Following these best practices ensures your DAX measures remain responsive even with millions of rows.

• Prefer ALLEXCEPT over multiple ALL(Column) – When preserving filters on only 1-2 columns out of many, ALLEXCEPT produces cleaner and often more efficient code
• Avoid ALLEXCEPT in iterators – Using ALLEXCEPT inside SUMX or FILTER over large tables creates nested context transitions that degrade performance
• Use variables – Store intermediate CALCULATE results in VAR statements to prevent redundant evaluations of the same ALLEXCEPT pattern
• Test with DAX Studio – Use the Server Timings feature in DAX Studio to compare the performance of ALLEXCEPT vs alternative approaches
• Consider relationships – ALLEXCEPT only removes filters from the specified table. Filters propagating through relationships from other tables remain active unless explicitly addressed.

Why Choose EPC Group for Power BI Consulting

EPC Group's Power BI practice brings 29 years of enterprise Microsoft consulting experience to every engagement. As a former Microsoft Gold Partner (2003–2022, the oldest in North America) and current Microsoft Solutions Partner, our DAX experts have designed complex analytical models for Fortune 500 organizations across healthcare, finance, manufacturing, and government. Our founder, Errin O'Connor, authored the bestselling Microsoft Press book on Power BI, and our team has delivered hundreds of enterprise Power BI implementations that leverage advanced DAX patterns like ALLEXCEPT for production-grade reporting.

• Advanced DAX development for complex business calculations and KPI frameworks
• Performance optimization for large-scale models with billions of rows
• Row-level security implementation for multi-tenant and compliance-driven environments
• Training programs that elevate your team's DAX proficiency from basic to advanced

Frequently Asked Questions