DAX Table Functions: CALCULATETABLE + INTERSECT + EXCEPT + VALUES

Leonardo Karpinski

Leonardo Karpinski

Power BI Master and Microsoft Certified Trainer, owner of the Power BI Experience. Graduated more than 20,000 students and participated in extensive projects for global companies.

Hi guys!

Since today is my birthday, the lesson will be a little different than usual. So, I can’t be live with you, but I prepared a special gift for you: a recording of a class from my Power BI eXperience course!! In this lesson we talk about Dax Table functions such as: Calculate table + intersect + except + values.

This is an advanced DAX problem that I solved with table manipulation functions.

There will be no materials for this lesson, ok? And this is the last video from 2020.

We return on January 4th, 2021, with Power BI Week event. Don’t forget to enroll!

Let’s get started!

We are studying and trying to solve a problem for HPN:

“Something very important in HPN’s business model is cross-selling. This is the case of BCAA sales combined with Whey Protein. These are products that always need to be sold together. So do an analysis of how many sales combined contained Whey Protein, but did not have any BCAA products, as in the examples below:”

Figure 1: Table with the number of Ordes

In this case both products need to sold together. As per what we can see in the figure, these products were not sold together in 35 cases.

In order to solve these problems, let’s split them.

First: How many orders had Whey Protein?

Create a “new measure”:

Orders with Whey Protein =
CALCULATE (
    DISTINCTCOUNT ( Sales[SalesOrderNumber] ),
    ‘Product'[SubcategoryName] = ‘Whey Protein’
)

Figure 2: Number of Whey Protein orders

Now let’s see how many orders we had for BCAA.

Create “New measure”:

Orders with BCAA =
CALCULATE (
    DISTINCTCOUNT ( Sales[SalesOrderNumber] ),
    ‘Product'[SubcategoryName] = “BCAA”
)

Figure 3: Number of BCAA orders
Figure 4: Total numbers of orders

Now we are going to do something different. Instead of using “Calculate” function, let’s use “Table manipulation”. In other words, let’s first create a table with the 63 orders and then do a “Count rows”.

Orders with BCAA =
VAR vOrders =
    CALCULATETABLE (
        VALUES ( Sales[SalesOrderNumber] ),
        ‘Product'[SubcategoryName] = “BCAA”
    )
RETURN
    COUNTROWS ( vOrders )

Now let’s use both together:

Orders with BCAA =
VAR vOrdersBCAA =
    CALCULATETABLE (
        VALUES ( Sales[SalesOrderNumber] ),
        ‘Product'[SubcategoryName] = “BCAA”
    )
VAR vOrdersWheyProtein =
    CALCULATETABLE (
        VALUES ( Sales[SalesOrderNumber] ),
        ‘Product'[SubcategoryName] = “Whey Protein”
    )
RETURN
    COUNTROWS ( vOrdersWheyProtein )

Figure 5: Number of BCAA orders

We have two variables and in each of these two variables, we have tables, a set. What can we do with this set? In math we can find the intersection, we can find the exception, we can UNIR them, etc.

 Now I want to use both BCAA and Whey Protein sells by making an intersection between them. In DAX we have a very nice function that is called “INTERSECT”.

Orders with BCAA AND Whey Protein =
VAR vOrdersBCAA =
    CALCULATETABLE (
        VALUES ( Sales[SalesOrderNumber] ),
        ‘Product'[SubcategoryName] = “BCAA”
    )
VAR vOrdersWheyProtein =
    CALCULATETABLE (
        VALUES ( Sales[SalesOrderNumber] ),
        ‘Product'[SubcategoryName] = “Whey Protein”
    )
VAR vResult =
    INTERSECT (
        vOrdersBCAA,
        vOrdersWheyProtein
    )
RETURN
    COUNTROWS ( vResult )

Figure 6: Number of orders with both products]

So, according to the given number, 90 orders were for Whey Protein and 55 orders from those had BCAA.

What function can I use to know the exception for those tables? Meaning, what I have in one table that I don’t in other.

When using the “Except” function, the order matter. Pay attention to that!

Orders w/ Whey Protein w/out BCAA =
VAR vOrdersBCAA =
    CALCULATETABLE (
        VALUES ( Sales[SalesOrderNumber] ),
        ‘Product'[SubcategoryName] = “BCAA”
    )
VAR vOrdersWheyProtein =
    CALCULATETABLE (
        VALUES ( Sales[SalesOrderNumber] ),
        ‘Product'[SubcategoryName] = “Whey Protein”
    )
VAR vResult =
    EXCEPT (
        vOrdersWheyProtein,
        vOrdersBCAA
    )
RETURN
    COUNTROWS ( vResult )

Figure 7: Number of orders without BCAA

To build a matrix, let’s do like this:

Figure 8: Building a matrix
Figure 9: The intersection between both products

When we want to know the orders in common for both, we use the function “Intersect”. When we want to know the exception, we use “Except”, but you need to know from one table to another (because the order matters, remember?)

Orders w/ Whey Protein w/out BCAA =
VAR vOrdersBCAA =
    CALCULATETABLE (
        VALUES ( Sales[SalesOrderNumber] ),
        ‘Product'[SubcategoryName] = “BCAA”
    )
VAR vOrdersWheyProtein =
    CALCULATETABLE (
        VALUES ( Sales[SalesOrderNumber] ),
        ‘Product'[SubcategoryName] = “Whey Protein”
    )
VAR vResult =
    EXCEPT (
        vOrdersBCAA,
        vOrderWheyProtein
    )
RETURN
    COUNTROWS ( vResult )

We can see that the exception for BCAA is 8.

Figure 10: Finding the exception
Orders w/ Whey Protein w/out BCAA =
VAR vOrdersBCAA =
    CALCULATETABLE (
        VALUES ( Sales[SalesOrderNumber] ),
        ‘Product'[SubcategoryName] = “BCAA”
    )
VAR vOrdersWheyProtein =
    CALCULATETABLE (
        VALUES ( Sales[SalesOrderNumber] ),
        ‘Product'[SubcategoryName] = “Whey Protein”
    )
VAR vResult =
    INTERSECT (
        vOrdersBCAA,
        vOrderWheyProtein
    )
RETURN
    COUNTROWS ( vResult )

Figure 11: Total number of orders

So, these table manipulation functions are very powerful! You can do a lot of calculations using these functions.

That was it for today, guys!

I wish you all a Merry Xmas and Happy New Year!!

See you in 2021!

Cheers,
Leonardo

Share this post:
Share on facebook
Share on linkedin
Share on twitter
Share on pinterest