Power BI is a Business Intelligence and data visualization tool developed by Microsoft. It helps organizations make better data-driven decisions. Let's have a look at the Power BI ALLEXCEPT function.
Thanks to analysis, modeling, and customizable development, data processing becomes simpler. However, you may need to write a formula to tailor a table to your business, financial, or managerial needs.
This is where the Power BI ALLEXCEPT function comes into play. According to Microsoft documentation, the ALLEXCEPT function removes all filter contexts except those specified by the function. This article goes into detail on the syntax, parameters, and how to apply the ALLEXCEPT function in Power BI.
ALLEXCEPT function syntax in Power BI
The Power BI ALLEXCEPT function helps developers remove all context filters used in the table except for the filters specified by the user or used in the specified columns. The syntax is written as follows:
ALLEXCEPT(<table>, <column>[, <column>[, …]])
Function parameters
| Terms | Definition |
|---|---|
| <table> | The table on which all context filters are removed, with the exception of column filters, specified in the following arguments. |
| <column> | The column for which context filters are to be preserved. |
The first argument of the Power BI ALLEXCEPT function must be a reference to the base table. Therefore, all subsequent arguments must be references to base columns. However, the ALLEXCEPT function does not support table or column expressions.
How do I use the ALLEXCEPT function in Power BI?
The Power BI ALLEXCEPT function is not used on its own. It is indeed an intermediate function that can be used to modify the set of results on which calculations are performed. There are many scenarios in which the ALL and ALLEXCEPT functions are used. The list is provided by Microsoft below:
| Function and usage | Description |
|---|---|
| ALL(Table) | This function removes all filters from the specified table. ALL(table) returns all values in the table, removing any filters that would normally be applied. The function is particularly useful when developers are working on multiple levels of grouping and creating a calculation that generates a ratio between an aggregated value and the total value. |
| ALL (Column[, Column[, ...]]) | All filters on specified table columns are removed. All other filters on the table's other columns remain in effect. All column arguments must be derived from the same table. When you want to remove contextual filters from one or more specific columns while retaining all other contextual filters, the ALL(Column) variant comes in handy. |
| ALLEXCEPT(Table, Column1 [,Column2]...) | Remove all table context filters except those applied to specified columns. This is a handy shortcut if you want to remove filters from several, but not all, columns in a table. |
An example to help you understand the ALLEXCEPT function in Power BI
Let’s say you work in a store and you want to get a column with total orders by category. Today, you have two levels of context filters applied:
- Level 1: product category (furniture) ;
- Level 2: product sub-category (bookcases, chairs, office supplies, tables)
The aim is to keep the Level 1 filter, but not the second-level filter. This is where ALLEXCEPT comes into its own with the following function:
= CALCULATE(
[Total Orders],
ALLEXCEPT(Orders,Orders[Product Category])
)
In everyday use, we use a DAX function to :
- Remove all context filters, except those applied to the product category column.
- Calculate the sum total of orders in the undeleted column, by product category.
Conclusion
The Power BI ALLEXCEPT function allows you to remove all context filters used in a table, except for those specified by the user or used in the specified columns. It can be used in conjunction with other functions like ALL and CALCULATE to modify the set of results on which calculations are performed.
The syntax of the ALLEXCEPT function consists of two parameters: a reference to the base table and one or more references to base columns. It does not support table or column expressions. The ALLEXCEPT function can be used in various scenarios, such as when you want to remove context filters from one or more specific columns while keeping all other filters from the table, or when you want to add context filters to a specific column while preserving all other filters from the table.
