How to Use ALLEXCEPT in DAX in Power BI: Syntax and Examples

In Power BI, use ALLEXCEPT in DAX to remove all filters from a table except those on specified columns. It helps you keep context on certain columns while ignoring others for calculations like totals or averages.

Syntax

The ALLEXCEPT function syntax is:

• ALLEXCEPT(table, column1, column2, ...)

Here, table is the table you want to clear filters from except for the columns listed after it. The columns you specify keep their filter context, while all other filters on the table are removed.

DAX

ALLEXCEPT(TableName, TableName[Column1], TableName[Column2])

Example

This example shows how to calculate total sales ignoring all filters except the ProductCategory column.

DAX

Total Sales by Category = CALCULATE(
    SUM(Sales[SalesAmount]),
    ALLEXCEPT(Sales, Sales[ProductCategory])
)

Output

If the report filters by ProductCategory = 'Bikes', this measure sums all sales for 'Bikes' ignoring other filters like Region or Date.

Common Pitfalls

• Not specifying columns in ALLEXCEPT will cause an error; you must specify at least one column.
• Using columns from different tables than the one specified in ALLEXCEPT causes errors.
• Confusing ALLEXCEPT with ALL which removes all filters without exceptions.

DAX

/* Wrong: No columns specified, causes error */
CALCULATE(SUM(Sales[SalesAmount]), ALLEXCEPT(Sales))

/* Correct: Keep filter on ProductCategory */
CALCULATE(SUM(Sales[SalesAmount]), ALLEXCEPT(Sales, Sales[ProductCategory]))

Quick Reference

Function	Description	Example Usage
ALLEXCEPT	Removes filters from all columns except specified ones	ALLEXCEPT(Sales, Sales[ProductCategory])
ALL	Removes all filters from a table or column	ALL(Sales)
KEEPFILTERS	Keeps existing filters and adds new ones	CALCULATE(SUM(Sales[Amount]), KEEPFILTERS(Sales[Region] = "West"))

Key Takeaways

Use ALLEXCEPT to remove filters except on specified columns in a table.

Always specify columns from the same table to avoid errors.

ALLEXCEPT helps keep context on important columns while ignoring others.

Do not confuse ALLEXCEPT with ALL; ALLEXCEPT preserves filters on chosen columns.

Test your measure to ensure filters behave as expected in your report.