Power BI, developed by Microsoft, is a BI & Data Visualization Tool employed by data-driven organizations worldwide to improve the way organizations use Data Analytics to address today’s business challenges.
With real-time high-level analytics, sophisticated modeling, and custom development, Power BI makes dealing with data pain-free.
However, when using Microsoft Power BI, you will frequently find that you need to write formulae, expressions, or refilter tables for certain use cases to evaluate data and calculate numbers in order to address real-world business challenges. Enter Power BI ALLEXCEPT Function.
In this blog post, we will be discussing Power BI ALLEXCEPT Function, which according to the Power BI Documentation, “Removes all context filters in the table except filters that have been applied to the specified columns.” This blog post will discuss, in detail, the syntax required to enable the Power BI ALLEXCEPT Function, some note-worthy scenarios where some principles are applied, and examples to work with the Power BI ALLEXCEPT Function efficiently. Let’s begin.
What is Power BI?
Power BI
Microsoft Power BI is a Business Intelligence (BI) tool and Data Visualization platform that enables enterprises to analyze business data to generate data-empowered reports. Power BI is a collection of built-in tools, apps, and connections that can delve deeply into and work with data to produce actionable insights, immersive visuals, and interactive reports.
Power BI is a self-service Business Intelligence tool, which means you can simply combine, analyze, visualize, and create visually stunning reports. Power BI allows you to import data from a variety of sources, including Oracle, SAP, and a Data Warehouse of your choice. It can handle everything from a basic Excel file to large volumes of data. So in short, Power BI Charts, Graphs, KPIs, Reports, and Dashboards can be used to analyze data and provide interactive insights.
Key Features of Power BI
Power BI has shown to be a dependable and easy-to-use Data Analysis and Visualization solution. Let’s look at some of the important elements that have greatly contributed to its success.
- Easy Integrations: Power BI includes interfaces with multiple connectors that allow users to pull data from a variety of data sources.
- AI Support: Power BI users may leverage Artificial Intelligence (AI) techniques like Image Recognition and Text Analytics to prepare data, build Machine Learning models, and swiftly extract actionable insights from structured and unstructured data.
- Report Sharing: Power BI is designed to help teams create security that allows them to share access in a very controlled manner. Users can easily share their reports with other members of their team without jeopardizing data security.
- Real-Time Dashboards: Power BI can present real-time data and visualizations in any report or dashboard. Power BI dashboards change in real-time, helping users to solve problems and discover new opportunities in real-time.
- Customized Visualization: Power BI provides a high level of customization and allows customers to use its custom visualization library to create visuals that meet their specific needs. Furthermore, by utilizing open-source data-viz modules like R and Python, analysts can create highly configurable visualizations for their next Power BI report.
What is The Power BI ALLEXCEPT Function?
The Power BI’s ALLEXCEPT Function helps developers to redact out all the context filters used in the table except the filters specified by the user or used in the specified columns.
Syntax
ALLEXCEPT(<table>,<column>[,<column>[,…]])
Crucial Parameters
| Term | Definition |
| table | The table from which all context filters are removed, with the exception of filters on columns supplied in later arguments. |
| Column | The column where context filters must be kept. |
The Power BI ALLEXCEPT function’s first argument must be a reference to the base table. Hence, all subsequent parameters must be base column references. On the other hand, the Power BI ALLEXCEPT function does not support tabular or columnar expressions.
How to use Power BI ALLEXCEPT Function?
The Power BI ALLEXCEPT function is not utilized by itself, rather, the function is an intermediate one that can be used to alter the set of results on which calculations are performed. There exist certain many scenarios where ALL and Power BI ALLEXCEPT functions are used. The list is provided below:
| Function & Usage | Description |
| ALL (Table) | Removes all filters from the table supplied. ALL(Table) effectively returns all of the values in the table, removing any filters from the context that could otherwise have been applied. This method is handy when working with multiple levels of grouping and need to build a computation that generates a ratio of an aggregated value to the total value. |
| ALL (Colimn[,Column[,…]]) | All filters from the specified columns in the table are removed; all other filters on other columns in the table remain in effect. All column arguments must be derived from the same table. When you want to remove the context filters for one or more specific columns while keeping all other context filters, the ALL(Column) variation is handy. |
| ALLEXCEPT (Table, Column1[,Column2]…) | All context filters in the table are removed except those that are applied to the chosen columns. This is a handy shortcut for cases where you wish to remove the filters from many, but not all, of the columns in a table. |
Note: When used in computed columns or row-level security (RLS) rules, this function is not supported in DirectQuery mode.
Examples To Better Understand Power BI ALLEXCEPT Function
The following measure formula given below adds SalesAmount USD and uses the ALLEXCEPT function to remove any context filters on the DateTime table that have not been applied to the CalendarYear column.
= CALCULATE(SUM(ResellerSales_USD[SalesAmount_USD]), ALLEXCEPT(DateTime, DateTime[CalendarYear]))
Because the formula employs ALLEXCEPT, if any column other than CalendarYear from the table DateTime is used to slice a visualization, the formula will remove all slicer filters, returning a value equal to the sum of SalesAmount USD. The findings are different when the column CalendarYear is used to slice the visualization. Because CalendarYear is supplied as an argument to ALLEXCEPT, when the data is sliced on the year, a filter on years at the row level is applied.
Let’s understand Power BI ALLEXCEPT with a practical example:
Step 1: Open the link to download the Sample Data: SuperStoreUS-2015.xlxs
Step 2: From the Visualization Pane, drag Table & Slicers.
- Slicers: In the first slicer, drag Product Category, and in the second slicer, drag Product Subcategory.
- Table: Drag three fields from the Orders Dataset into the table: Product Category, Product Sub Category, and Sales.
Step 3: Now, Create Measure and write the DAX Formula to execute further with ALLEXCEPT function.
ALLEXCEPT_SALES =
CALCULATE (
SUM ( Orders[Sales] ),
ALLEXCEPT ( Orders, Orders[Product Category] )
)
Step 4: The next step is to drag ALLEXCEPT_SALES measures into the table.
Step 5: Now, apply a filter to the Product Category and look at the ALLEXCEPT SALES result, which returns the Total Sales amount of Furniture.
Step 6: Now, apply filters to both slicers and observe the results.
Conclusion
In this blog post, we extensively talked about the Power BI ALLEXCEPT Function in detail with certain use cases and real-world examples. And if you want to learn more about how Power BI ALLEXCEPT Function can help, the below-mentioned Power BI Documentations can help a great deal.
- ALLEXCEPT function (DAX)
- Data Analysis Expressions (DAX) Reference
Visit our Website to Explore Hevo
Power BI is a great tool for performing Data Analytics and Visualization for your business data. However, at times, you need to transfer this data from multiple sources to your PowerBI account for analysis. Building an in-house solution for this process could be an expensive and time-consuming task.
Hevo Data, on the other hand, offers a No-code Data Pipeline that can automate your data transfer process, hence allowing you to focus on other aspects of your business like Analytics, Customer Management, etc. This platform allows you to transfer data from 100+ sources to BI tools like Power BI, and Cloud-based Data Warehouses like Snowflake, Google BigQuery, Amazon Redshift, etc. It will provide you with a hassle-free experience and make your work life much easier.
Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand.
Yash is a Content Marketing professional with over three years of experience in data-driven marketing campaigns. He has expertise in strategic thinking, integrated marketing, and customer acquisition. Through comprehensive marketing communications and innovative digital strategies, he has driven growth for startups and established brands.
