Power BI ALLEXCEPT Practical Examples for Insights

Power BI is a data visualization and business intelligence tool that helps organizations use data analytics to address business challenges.

Power BI users will likely have to write formulas, expressions, or refilter tables for certain use cases. This will, in turn, enable the user to evaluate data and calculate numbers so as to address real-world business challenges.

Table of Contents:

1. What is the Power BI ALLEXCEPT?
2. Why Use ALLEXCEPT in Power BI?
3. How Does ALLEXCEPT Work in DAX?
4. How to Evaluate ALLEXCEPT in Power BI?
5. How Do You Visualize Your Data in Power BI?
6. Wrap Up

First…

What is the Power BI ALLEXCEPT?

The ALLEXCEPT function in Power BI is a powerful tool for managing filters in Power BI.

It removes all context filters in the table, except for those applied to the specified columns, allowing for precise control over your data analysis.

ALL function: It removes all filters from a table or column.

• EXCEPT function: It removes the filters from the specified columns, and it keeps only those from other columns.
• ALLEXCEPT function: It retains filters on specified columns and removes filters from all other columns.

Here’s an example:

• TotalSales = CALCULATE(SUM(’Sales’[Amount]), ALLEXCEPT(’Sales’, ‘Sales’[Product]))

Why Use ALLEXCEPT in Power BI?

ALLEXCEPT function in Power BI comes in handy in scenarios where users want to perform calculations on a table while maintaining certain filters but removing others. The function plays a vital role in situations where the user needs to isolate specific dimensions for analysis.

For instance, when calculating the total sales for each product category while disregarding filters on other columns, you should use ALLEXCEPT(’Sales’, ‘Sales’[ProductCategory]). It ensures that filters on the ‘ProductCategory’ column are preserved. That provides a context for the calculation, and filters on other columns (like region or date) are removed.

The result is a focused analysis that allows you to examine the desired dimension’s contribution to the calculation without interference from unrelated filters. By combining the ALLEXCEPT function with the CALCULATE function in Power BI, you can enhance the flexibility of DAX calculations, enabling more precise and targeted data analysis.

How Does ALLEXCEPT Work in DAX?

The ALLEXCEPT function in DAX is used to control the filter context within a calculation. It removes filters from all columns in a table except for the specified ones, enabling focused calculations on specific dimensions while maintaining the context of those columns. When combined with DAX variables, ALLEXCEPT enhances the flexibility and clarity of your formulas, making it easier to manage complex calculations and improve performance.

Here’s the breakdown of how ALLEXCEPT works in DAX:

ALLEXCEPT(<table>, <column1>, <column2>, …)

• <table>: The table from which to remove filters.
• <column1>, <column2>, … Columns for which filters should be retained. These columns define the context of your calculation.

How it Works:

• Preservation of Columns: The columns specified in ALLEXCEPT determines which columns should retain their filters.
• Removal of Filters: Filters on all other columns in the specified table are removed. This ascertains that the calculations are not influenced by filters on unrelated dimensions.
• Calculation in the New Context: The subsequent calculation is performed within the modified filter context. Only the retained filters are considered, while filters on other columns are ignored.

Use Cases:

• Focused Analysis: It allows you to focus calculations on specific dimensions while disregarding filters on unrelated columns.
• Custom Aggregations: It’s useful for creating custom calculations or aggregations tailored to selected dimensions, allowing you to work effectively with aggregated data for deeper insights.
• Isolation of Dimensions: It enables the isolation of dimensions of interest within a calculation for more detailed analysis.

How to Evaluate ALLEXCEPT in Power BI?

In Power BI, the ALLSELECT function is used to evaluate a table expression. It removes all filters except those explicitly defined using the visual elements (or slicers).

The ALLEXCEPT function, on the other hand, is used to remove filters from all columns except the ones specified.

Here’s how to use ALLEXCEPT in Power BI:

• ALLEXCEPT(<table>, <column1>, <column2>, …)

<table>: The table from which you want to remove filters.

<column1>, <column2>, … The columns for which you want to retain filters.

Example

If there’s a table named Sales with columns Product, Region, and Date, and you need to calculate the total sales for all products in a specific region, regardless of the date filter, Power BI makes this simple. To ensure clarity, use the appropriate Power BI date format while working with the Date column. Here’s how it’s done.

Total Sales (All Except Date) =

CALCULATE(

SUM(Sales[SalesAmount]),

ALLEXCEPT(Sales, Sales[Product], Sales[Region])

)

How Do You Visualize Your Data in Power BI?

Stage 1: Log into Power BI, enter your email, and click “Submit.”

• Log in to Power BI.
• Enter your email address and click the “Submit” button.

• You are redirected to your Microsoft account.
• Enter your password and click “Sign in“.

• You can choose whether to stay signed in.

• Once done, the Power BI home screen will open.

Stage 2: Create a Data Set and Select the Data Set to Use in the Sankey Chart

• Go to the left-side menu and click the “Create” button.
• Select “Paste or manually enter data“.

• We’ll use the sample data below for this example.

Quarters	Vendors	Market Share
Q1	Samsung	27.69
Q1	Apple	28.45
Q1	Xiaomi	11.8
Q1	Huawei	6.53
Q1	Oppo	5.3
Q1	Vivo	4.19
Q2	Samsung	28.14
Q2	Apple	27.58
Q2	Xiaomi	12.62
Q2	Huawei	6.17
Q2	Oppo	5.5
Q2	Vivo	4.21
Q3	Samsung	28.45
Q3	Apple	27.71
Q3	Xiaomi	12.9
Q3	Huawei	6
Q3	Oppo	5.29
Q3	Vivo	4.17
Q4	Samsung	27.97
Q4	Apple	27.62
Q4	Xiaomi	12.68
Q4	Huawei	5.17
Q4	Oppo	6.07
Q4	Vivo	4.66

• Paste the data table into the “Power Query” window, and name it “Market Share Analysis.” Next, select the “Create a dataset only” option.

• Navigate to the left-side menu, and click on the “Data Hub” option. Power BI will populate the data set list. If no data set has been created, you’ll get an error message.

• Click on “Create report.”

• You can click on Market Share Analysis.

• Click on “Expand All.”

• To see your chart metrics, click on “Expand All.”

• Click on “Get more visuals.”

• Search ChartExpo and select “Comparison Bar Chart.”

• Click on “Add.”

• After that, you’ll see the Comparison Bar Chart in the visuals list.

• You can expand the chart space.

• After that, select the metrics of your data.

• Click on the second icon of Format Visuals and click on Visual.

• In Visual, click on License Settings and add the key. After adding the key, you’ll see the comparison bar chart.

• To add the header text, click on General. Next, add the header text in the title.

• Here’s the final look at the Comparison Bar Chart in Power BI.

Insights

• Samsung and Apple are the top two vendors in terms of market share, and Samsung takes the lead.
• Xiaomi has been gaining market share, while Huawei has been losing market share.
• Vivo and Oppo have been relatively stable in terms of market share. In the last quarter, there was a transition between Huawei and Oppo.

Transform Visual Analysis by Leveraging Power BI ALLEXCEPT with Charts:

1. Open your Power BI Desktop or Web.
2. From the Power BI Visualizations pane, expand three dots at the bottom and select “Get more visuals”.
3. Search for “Comparison Bar Chart by ChartExpo” on the AppSource.
4. Add the custom visual.
5. Select your data and configure the chart settings to create the chart.
6. Customize your chart properties to add header, axis, legends, and other required information.
7. Share the chart with your audience.

The following video will help you create a Comparison Bar Chart in Microsoft Power BI.

FAQs

What does ALLEXCEPT do in DAX?

In DAX, the ALLEXCEPT function removes filters from all columns in a specified table except for the columns explicitly mentioned. It also allows focused calculations on selected dimensions while preserving their filter context.

What does ALLEXCEPT mean?

“All Except” refers to a function in DAX used in Power BI. ALLEXCEPT removes filters from all columns in a table except for the ones specified, and that enables focused calculations on selected dimensions.

What does ALLEXCEPT DAX function return a table?

Yes, ALLEXCEPT DAX function returns a table. Filters are removed from all columns in a specified table except for the columns explicitly listed. This creates a modified table with a refined filter context.

Wrap Up

ALLEXCEPT in Power BI helps the user perform focused analyses on specific dimensions while retaining context. It boosts flexibility, isolates dimensions for detailed insights, and gives room for more targeted and precise data analysis.

Visualization in Power BI improves understanding by presenting data insights graphically. It helps the user evaluate ALLEXCEPT results and also provides a clear, intuitive representation of filtered data. It also supports informed decision-making.

The ALLEXCEPT function plays a vital role in complex calculations where isolating specific dimensions is crucial. It helps in facilitating the creation of sophisticated DAX formulas that respond to changing filter contexts.

Furthermore, the function helps the user to achieve more consistent and predictable results in cases where they have to control the impact of filters on a calculation.

By now, you should know how to create the Comparison Bar Chart in Power BI and the benefits of the ALLEXCEPT function in Power BI. It’s time to perform more complex data analyses for your organization.