What is ALLExcept in Power BI?

If you're creating reports in Power BI, you've likely spent some time wrestling with DAX (Data Analysis Expressions). One of the most powerful, yet often confusing, functions in this language is ALLEXCEPT. This article will show you what ALLEXCEPT does, how it works, and how you can use it to build dynamic and insightful reports.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

First, a Quick Primer on Filter Context

Before we can understand ALLEXCEPT, we need to quickly touch on a core Power BI concept: filter context. Think of filter context as the set of "active" filters applied to your data at any given moment.

Imagine you have a simple table showing sales by product category:

• Coffee: $10,000
• Tea: $8,000
• Pastries: $5,000

When Power BI calculates the sales for the "Coffee" row, the filter context is "Product Category = Coffee." It only looks at the sales data that matches this criterion. When it moves to the "Tea" row, the filter context changes to "Product Category = Tea," and so on.

Filters can come from anywhere: rows in a table, columns in a matrix, slicers, or other visuals on your report page. The ability to manipulate this filter context is what makes DAX so powerful. Functions like ALLEXCEPT are the tools that let you do it.

What is the ALLEXCEPT Function?

At its heart, the ALLEXCEPT function tells Power BI to remove all filter context from a table, except for the filters on the columns you specify.

It’s like saying, "calculate this for everything, but still pay attention to the filter on this one specific column."

The DAX syntax for ALLEXCEPT is:

ALLEXCEPT(<table>, <column1>, [<column2>, ...])

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Syntax Breakdown:

• <table>: The table from which you want to remove filters. This is usually your main fact table, like a 'Sales' or 'Orders' table.
• <column1>, <column2>, ...: The columns whose filters you want to keep active. Any filter applied to these columns will remain in place, while filters on all other columns in the table will be ignored.

ALLEXCEPT is a table function, which means it returns a table. Because of this, you’ll almost always find it used inside the CALCULATE function, which allows you to modify the filter context for a specific calculation.

ALLEXCEPT vs. ALL: The Key Difference

A common point of confusion is the difference between ALLEXCEPT and its cousin, ALL. Both are used within CALCULATE to modify filters, but they work in opposite ways.

• ALL(<table>): Removes all filters from the entire specified table. It’s a complete reset. Use this when you want to calculate a grand total against which you can compare everything else.
• ALLEXCEPT(<table>, <column1>, ...): Removes all filters from the table except the ones on the specified columns. It’s more of a targeted reset.

Think of it this way:

• ALL says: "Ignore all the filters here."
• ALLEXCEPT says: "Ignore all filters, except for this one."

This subtle difference has a major impact on your calculations, as we'll see in the examples.

Practical Examples: Using ALLEXCEPT in Your Reports

Theory is great, but let's see how this works in practice. The most common use case for ALLEXCEPT is calculating percentages, such as the percent of a category total.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Example 1: Calculating Percentage of Category Total

Let's say you have a sales data table with columns for Product Name, Product Category, and Revenue. You want to build a visual that shows each product's sales and what percentage of its category's total sales it represents.

Here’s the breakdown:

To calculate the "% of Category Total" for Espresso, you need two numbers: Espresso's revenue ($1,500) and the total revenue for the entire 'Coffee' category ($5,000).

Step 1: Create a basic sales measure.

First, we need a simple measure to sum up the revenue. This respects the default filter context.

Total Sales = SUM(Sales[Revenue])

Step 2: Create a measure for the category total using ALLEXCEPT.

Next, we create a measure that calculates the total sales for the category of whatever row we're on. This is where ALLEXCEPT comes into play.

Category Total Sales = CALCULATE( [Total Sales], ALLEXCEPT(Sales, Sales[Product Category]) )

Let’s break down what this does. When this measure is calculated for the "Espresso" row:

1. The filter context is Sales[Product Category] = "Coffee" and Sales[Product] = "Espresso".
2. ALLEXCEPT(Sales, Sales[Product Category]) tells CALCULATE to remove all filters from the Sales table, except for the one on Sales[Product Category].
3. This effectively removes the filter for Sales[Product] = "Espresso" while keeping the filter for Sales[Product] = "Coffee".
4. CALCULATE then computes [Total Sales] in this new context, giving you the total for all products in the "Coffee" category.

Step 3: Create the final percentage measure.

Now that you have both pieces, you can divide one by the other to get the percentage.

% of Category Total = DIVIDE( [Total Sales], [Category Total Sales] )

When you add this measure to your table visual, you'll see a result like this:

As you can see, the [Category Total Sales] column changes its value only when the category changes, which is a perfect denominator for our calculation.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Example 2: Analyzing Performance Across Multiple Contexts

You can preserve more than one filter. Let's expand our data model to include a 'Date' table and analyze sales by year and category.

Imagine you want to see each product's percentage of sales within its category for that year. This requires preserving the filters on both Product Category and Year.

The DAX measure would look like this:

Category & Year Sales = CALCULATE( [Total Sales], ALLEXCEPT( Sales, Sales[Product Category], 'Date'[Year] ) )

This measure removes all filters except for the ones on the product category and the year. You could then use this to calculate something like % of Category & Year Total, giving you a powerful way to compare product performance year over year within the context of their specific categories.

Common Pitfalls and Best Practices

While powerful, there are a few things to keep in mind when using ALLEXCEPT.

• Stick to the Same Table (Mostly): The columns listed in ALLEXCEPT should belong to the table specified in the first argument. A powerful feature is that you can also reference columns from related dimension tables (like 'Date'[Year] in our second example), which extends its functionality.
• Use What's Needed: Sometimes, a simpler function like ALL('Product'[Product Category]) might achieve the same goal as ALLEXCEPT(Sales, 'Product'[Product Category]) if your model is set up correctly. Always opt for the simplest DAX that gets the job done.
• Think About Your Question: Before writing any DAX, clearly state the question you want to answer. For our first example, the question was: "What is the total sales for this product's entire category?" Framing the question makes it easier to choose the right functions.

Final Thoughts

Mastering ALLEXCEPT is a significant step toward becoming proficient in Power BI. By giving you precise control over filter context, it lets you create layered and comparative metrics that go far beyond simple sums and averages. It helps you answer deeper business questions by comparing individual performance to a broader, but still relevant, whole.

Of course, becoming a DAX expert involves a steep learning curve. The process of tweaking functions, debugging measures, and understanding filter contexts can take time. At Graphed, we've automated this entire process. We allow you to connect your data sources and create reports just by describing what you want to see in plain English. Instead of writing complex DAX measures, you can simply ask, "show me each product's sales as a percentage of its category's total," and we'll generate the analysis for you instantly.