ALLEXCEPT() in DAX and Power BI: Syntax, Examples & Guide

Posted by

October 6, 2026

On September 10, 2026

Introduction

While creating Power BI reports, the output of a measure depends on the filters used in the visualization, slicers, or other visuals of the report. Sometimes, you want a calculation to ignore those filters. In other cases, you may want to remove most filters while preserving the filter applied to one or more important columns. The DAX ALLEXCEPT() function proves itself useful here. We will look at ALLEXCEPT() in this tutorial, including its syntax and how it can be used with a Product table in Power BI.

You can download the Power BI file through the link located below the blog post.

What does ALLEXCEPT() mean in DAX?

ALLEXCEPT() is a function which alters the filter context.

Its basic syntax is:

The first argument must be a base table, and all the remaining arguments must be referenced to base columns in that table; if you need to retain filters on more than one column, you can specify multiple columns.

How ALLEXCEPT() Works

Consider that you have a Product table with fields:

Product Key.
Product Name.
Category.
Subcategory.
Color.
Brand.
Product Price.

And you use the following expression:

ALLEXCEPT ( Product, Product[Category] )

Power BI removes filters from the Product table except the filter on Category.

So, if a report is filtered by:

Category = Bikes
Color = Black
Brand = Contoso

ALLEXCEPT(Product, Product[Category]) preserves the Category filter but removes the filters coming from the other Product columns.

Why Use ALLEXCEPT()?

Using ALLEXCEPT() becomes more effective when you need to calculate values for a certain level of grouping.

The following situations are typical:

To calculate totals per category.
Comparing the value to the category total.
Creating percentage of calculations.
Preserving Year while ignoring other Date filters.
Creating calculations depending on a particular hierarchy level.
Controlling filter context inside CALCULATE().

Microsoft describes ALLEXCEPT() as an intermediate function that is generally used to modify the set of results over which another calculation is performed.

Related Reading: If you are working with time-based calculations in Power BI, you may also find our guide on PARALLELPERIOD() in Power BI useful. It explains how PARALLELPERIOD() can be used for previous-year and other period comparisons.

Using ALLEXCEPT() in Power BI

ALLEXCEPT() is a filter-context function. It removes filters from a specified table while preserving filters on the columns listed in its arguments. It does not remove columns from a table or mean “all columns except.”

Let’s look at an example where we want to remove filters from the Product table while preserving the filter on Category.

Suppose we want to calculate total sales while keeping the filter on Product Category, regardless of filters applied to other columns in the Product table. We can use ALLEXCEPT() inside CALCULATE().

DAX

Total Sales =

SUM ( Sales[Sales Amount] )

Then, follow these steps:

Click the Modelingtab in the Ribbon.
Click New measure.
Enter the following DAX expression:

DAX

Total Sales by Category =

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

Key Points to Remember

When you are working with the ALLEXCEPT() function there are some things you need to keep

The first argument must be a base table.
Subsequent arguments must be base column references.
You can specify multiple columns.
The function modifies filter context, not the physical columns in a table.
It is commonly used with CALCULATE().
Filters on specified columns are preserved.
ALLEXCEPT() is useful for calculations that need to maintain a particular grouping level.
It is not the correct function for simply excluding a column from a calculated table.

Common Mistake with ALLEXCEPT()

One of the most common mistakes is interpreting the function name as:

“All columns except…”

That interpretation is incorrect.

Instead, think of it as:

“Remove all filters from this table except the filters on these columns.”

This simple distinction makes it much easier to understand how ALLEXCEPT() works in DAX.

When Should You Use ALLEXCEPT()?

Use ALLEXCEPT() when:

You need to remove multiple filters from a table.
You want to preserve one or more specific filters.
You are building a measure with CALCULATE().
You need category-level or group-level calculations.
You want a calculation to ignore detailed filters while respecting a higher-level grouping.

If you only want to remove a filter from one particular column, ALL(Column) may be simpler. If you need to remove filters from an entire table, ALL(Table) may be more appropriate.

Conclusion

ALLEXCEPT() function of DAX in Power BI is one of the most useful functions in order to control the filter context. Instead of removing every filter, it allows you to remove filters from a table while preserving filters on specific columns.

The first thing you have to keep in mind is that ALLEXCEPT() works with filters and not with columns. In case you want to specify which columns should be shown in a new calculated table, choose other DAX functions for this purpose. Once you understand this distinction, ALLEXCEPT() becomes particularly useful when you need to perform calculations while ignoring filters on certain columns but retaining the filter context of specific columns, such as Category, Region, or Department.

FAQs

What is the purpose of ALLEXCEPT() in DAX?

The ALLEXCEPT() is used to remove any filters that have been applied to a table except for the filters on the columns that you specify.

Is ALLEXCEPT() capable of dealing with more than one column?

Yes, the ALLEXCEPT() function can handle than one column.

ALLEXCEPT ( Product, Product[Category], Product[Brand] )

This means that the ALLEXCEPT() function will keep the filters on the Category and Brand columns of the Product table.

Is it possible to use ALLEXCEPT() with CALCULATE()?

Yes, you can use the ALLEXCEPT function with the CALCULATE function.

Extended Learning & References

If you’re looking to deepen your Power BI knowledge and build practical, job-ready skills, explore these professional training courses offered by Power BI Training. These courses cover everything from the fundamentals to advanced reporting, DAX, and Microsoft Copilot integration.

These courses provide hands-on learning, real-world examples, and industry best practices to help you enhance your Power BI expertise and confidently build modern business intelligence solutions.