DAX Table Functions: CALCULATETABLE + INTERSECT + EXCEPT + VALUES

Publicações

DAX Table Functions: CALCULATETABLE + INTERSECT + EXCEPT + VALUES

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’
)

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

Create “New measure”:

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

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 )

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 )

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

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.

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 )