Deep Dive into the EXCEPT & INTERSECT Functions - DAX Review in Power BI

[EnterpriseDNA]

There are hundreds of functions used in DAX formulas; they can be confusing sometimes, especially when you’ve just started with Power BI DAX. 

This article features the DAX functions, INTERSECT and EXCEPT. Both are used to get or return tables, but there’s a slight difference between these functions. 

INTERSECT compares one virtual table to another and it specifically looks for similar rows. When you use INTERSECT in a formula, you’ll be able to return a table of results that appear in both versions of those two virtual tables. The EXCEPT function, on the other hand, gets the rows that are not the same. It returns the rows of a table that don’t appear in another table. 

I’ll share with you some of my tutorials where I used these functions to better understand how they’re utilized in a formula. 

First, let’s dive into the INTERSECT function. Watch the tutorial below, which showcases an analysis that uses this function specifically to extract unique insights. 

In this tutorial, I go through what this function is and how you can use it for some advanced Power BI analyses. Once you get a deeper understanding of virtual tables, which INTERSECT can create, you’ll find that there are many applications for using INTERSECT. 

When you understand how you can utilize this function, you’ll see that it has an amazing capability when running some very advanced logic. 

This next tutorial introduces the amazing things you can do with the basket analysis technique. Here I analyze the customer sales of a group of products versus another. I used the INTERSECT function to evaluate which customers are part of the initial basket and are included in the selected basket. 

Watch the entire tutorial and see how I used INTERSECT in the formula. 

There are plenty of applications for this analysis across Power BI. By running this type of analysis, you can calculate the sales trends or purchasing behavior of your customers. But for this tutorial, I share with you my best practices when it comes to using this function and technique. 

Now, let’s talk about the EXCEPT function. This function is used to exclude similar rows that are found in one table but not in another.  In other words, it returns rows that are unique to a result. Here’s a tutorial wherein I used EXCEPT together with CALCULATETABLE and COUNTROWS. 

Here, I compare the current customer set to a customer set from a prior period. I consider customers as new if they have only purchased just now but haven’t bought anything in the last 90 days. To achieve this, I combined the CALCULATETABLE function with the table function EXCEPT.  

The EXCEPT function in the formula evaluates two tables and returns the customers that are in the first table, but not on the second one. 

Key Take Away 

The INTERSECT and the EXCEPT functions in Power BI are advanced DAX table functions and you need to understand them better so you can maximize their usage and capabilities. 

You can do so much with INTERSECT. For example, you can find out the products that have been bought this month and last month. The EXCEPT function is quite similar, but it’s actually the reverse of the INTERSECT function, so it would show you the products that have been sold this month, but not last month, for example. And you could go even further with your analysis by combining these functions with other key functions like COUNTROWS. 

Once you understand this simple concept and the difference between these two table functions, you’ll be able to use them easily and effectively in your analysis. If you want to learn more about DAX functions, check out the related links and courses below. 

Enjoy! 

Sam 

***** Related Links ***** 

Using ALLEXCEPT To Stop The Cumulative Total From Resetting 

A Deep Dive Into How The ALLSELECTED DAX Function Is Used In Power BI 

ALL Function In Power BI – How To Use It With DAX 

***** Related Course Modules***** 

Mastering DAX Calculations 

DAX Formula Deep Dives 

Advanced DAX Combinations 

***** Related Support Forum Posts ***** 

Confirming My Understanding Of INTERSECT 

Thoughts About “New Customer Analysis” 

Dax Not Working 

For more INTERSECT and EXCEPT queries to review see here…..