Mastering ALLEXCEPT for Selective Filter Removal

Introduction

In our last lesson, we mastered calculating percentages of grand totals and subtotals by using CALCULATE with the ALL function to remove specific filters. We saw how ALL('Sales') removes all filters for a grand total, and ALL('Product'[Color]) removes just the color filter for a subtotal.

As we discussed, writing ALL for many individual columns can become tedious. This lesson introduces a more elegant and readable alternative: the ALLEXCEPT function. You will learn how to use ALLEXCEPT to remove all filters from a table except for the ones you want to keep. This is a powerful and common pattern for calculating contributions to a subtotal, like a product's sales as a percentage of its category's total sales.

By the end of this 60-minute lesson, you will be able to confidently use ALLEXCEPT to create sophisticated subtotal calculations and understand the critical nuances of how it interacts with the filter context.

The Power of "All Except..."

Imagine a report with a table showing Region, Country, and Net Sales. A common business question is, "What were the total sales for each region?" and "How did each country contribute to its regional total?"

With the tools from our last lesson, you'd calculate the regional total by removing the filter on the Country column. But what if the table had Country, State, and City? You would have to remove filters from State and City to get the Country total. ALLEXCEPT simplifies this. You just tell DAX which filter to keep.

ALLEXCEPT is a CALCULATE modifier. Its syntax is:
ALLEXCEPT(<table>, <column_to_keep_filter_on_1>, <column_to_keep_filter_on_2>, ...)

It instructs CALCULATE to remove all filters from the <table> except for any filters applied to the columns you list.

A Practical Example: Percentage of Category Revenue

Let's apply this to our sales data. We want to calculate each product's revenue as a percentage of its parent category's revenue.

Our formula structure remains the same: DIVIDE(Part, Whole).

• Part: [Total Revenue] (evaluated in the current context, e.g., for a specific product).
• Whole: The total revenue for the product's parent category. We'll use ALLEXCEPT to calculate this.

Here is the DAX measure. Notice how readable it is using variables.

% of Category Revenue (ALLEXCEPT) = 
VAR Denominator = 
    CALCULATE(
        [Total Revenue],
        ALLEXCEPT('Product', 'Product'[Category])
    )
RETURN
    DIVIDE(
        [Total Revenue], 
        Denominator
    )

How it works:
The Denominator variable is the key. ALLEXCEPT('Product', 'Product'[Category]) tells CALCULATE to compute [Total Revenue] under a modified filter context where all filters from the Product table are removed, except for the filter on Product[Category]. This gives us the total revenue for the category, which is exactly what we need for our subtotal calculation.

For a detailed, hands-on walkthrough of building this type of measure from scratch, the following article provides an excellent step-by-step guide. I encourage you to follow along in your Power BI file.

A Critical Detail: ALLEXCEPT Only Preserves Filters

You have a preference for understanding the "why," which is essential for avoiding common DAX pitfalls. ALLEXCEPT has a crucial behavior you must understand: it can only preserve a filter if that filter already exists in the evaluation context. It cannot create a new filter.

This can lead to unexpected and incorrect results if you're not careful. Let's watch a video from the experts at SQLBI who explain this nuance perfectly.

This is a subtle but vital concept. The behavior you just witnessed is why some DAX practitioners prefer a more robust (though slightly more complex) pattern using ALL and VALUES, which we may explore later. For now, the key is to be aware that a measure using ALLEXCEPT may depend on the columns present in your visual.

ALLEXCEPT as a Table Function vs. a CALCULATE Modifier

Finally, it's important to know that, like ALL, ALLEXCEPT has two distinct uses. Its primary use is as a CALCULATE modifier, which is what we've focused on. However, it can also be used as a table function on its own. The results are very different, and confusing them is a frequent source of errors.

Let's watch one last short clip to see the difference clearly.

Conclusion

You've now added another powerful tool to your DAX toolkit for modifying the filter context. ALLEXCEPT offers a clean and readable way to perform calculations against a subtotal.

Key Takeaways:

• ALLEXCEPT removes all filters from a table except for filters on the columns you specify.
• It is most commonly used inside CALCULATE to create denominators for "percentage of parent/subtotal" calculations.
• The critical rule: ALLEXCEPT only preserves filters that are already active in the current context. It cannot add new filters. This can make measures fragile if the visual layout changes.
• Using ALLEXCEPT as a standalone table function produces a different result than using it as a CALCULATE modifier, as it returns a physical table and ignores the filter context.

Preview of the Next Lesson:
We have spent the last few lessons mastering how to manipulate the filter context. Now, we're going to pivot to another foundational DAX concept: the row context. In the next lesson, we will introduce iterator functions (like SUMX). These functions allow you to perform calculations row by row before aggregating the final result, which is essential for many real-world scenarios, such as calculating total revenue when your data only contains Unit Price and Order Quantity columns.