ALLEXCEPT in Action: Building Precise Calculations in Power BI

If you need to build calculations with a finer level of precision over the filter context, this article is for you. We will see how the ALLEXCEPT function can help, with a detailed explanation, practical examples and best practices for using it, plus a few precautions to keep in mind.

What is the ALLEXCEPT function?

In DAX, the ALLEXCEPT function returns all the columns of a table except the ones specified as arguments. Its main use is to remove filters from every column of a table while keeping the filters on the columns you specify.

It is very similar to the ALL function, which disregards filter contexts. The difference is that here you can specify which filters and columns will not be disregarded.

Syntax

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

• Table: The table the function operates on.
• Column1, Column2, ...: The columns whose filters will be preserved.

Usage examples

Example 1: calculating total sales by category

Imagine a sales table with information about products, categories and sales amounts. If we want to calculate total sales for each category, ignoring filters on other columns, we can use:

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

In this case, ALLEXCEPT removes the filters on every column of the sales table except the Category column.

Example 2: reports with dynamic charts

In interactive dashboards, you may need to calculate the overall average within a fixed dimension, such as "Department":

Average by Department =
CALCULATE(
    AVERAGE(Sales[Amount]),
    ALLEXCEPT(Sales, Sales[Department])
)

This lets users see more focused comparisons in charts, ignoring unwanted context filters.

In the example above, the overall sales average is calculated considering only the Department filters.

Example 3: total sales by category and region

You want to calculate total sales, removing the filters on every column but preserving the filters applied to the Category and Region columns.

Total Sales by Category and Region =
CALCULATE(
    SUM(Sales[Amount]),
    ALLEXCEPT(Sales, Sales[Category], Sales[Region])
)

If you are viewing a report where the "Category = Electronics" and "Region = North" filters are active, this measure will respect those filters but ignore any other filter, such as date or product.

👍 Best practices when using ALLEXCEPT

• Understand filter contexts: Make sure you understand how row context and filter context interact in your calculations.
• Use it sparingly: ALLEXCEPT can affect performance, especially in large models, because it changes the filters on an entire table.
• Prefer relational tables: Whenever possible, keep a good data model to avoid the excessive need to manipulate filters.
• Combine it with other functions: ALLEXCEPT is often used together with CALCULATE and FILTER to build more complex calculations tailored to the goal.
• Use variables: Avoid creating several calculations that use ALLEXCEPT in the same measure. Use variables to improve performance.
• Group your data: If possible, group the data beforehand to avoid the need for ALLEXCEPT.

🚨 Precautions when using ALLEXCEPT

• Performance impact: In large models, overusing ALLEXCEPT can increase calculation time.
• Lack of clarity: Careless use can make the code harder for other developers to understand.
• Unintended filters: Make sure you preserve only the filters you need to avoid unexpected results.

Conclusion

The ALLEXCEPT function is a good ally for building dynamic, detailed analyses in Power BI. Understanding how it interacts with the data model and how it handles filters is key to getting the most out of it.

Remember that the balance between complexity and clarity should be a priority.