Use a measure or virtual table as filter for CALCULATE

This post is also available in: Deutsch

Last week, a colleague at work had an interesting question with a customer. This wanted to perform a calculation with CALCULATE, but only on the records of the table where a measure has a certain value. Or in more simple terms, he wants to filter the table by a measure and then perform the calculation on the filtered table.

Measure always requires filter context

A characteristic of measures is that they always require a filter context. To make a simple example, if my measure has the formula Sales Amount = SUM( myTableem[Sales Amount] ), then this formula needs a filter context. Do I want the sales numbers per product or by year? Or maybe by country or by customer? All these issues can be solved with the formula just mentioned, but the result depends on the filter context.

Best product by category

For this scenario, I would like to use a simplified example. We assume I have different fruits and they are divided into color categories:

ColorProduct
GreenKiwi
GreenAvocado
OrangeOrange
OrangeMandarin
OrangePeach
RedApple
RedCherry
YellowBanana
YellowLemon

Of course, I am interested in the sales of my fruits, which is why I analyze them in Power BI. More specifically, I’m interested in how per product color the particular bestseller performs. I can get the best seller per product color with a measure that returns the rank. Visually, this means I want to consider only the yellow highlighted rows for 2020:

Ranking in 2020
Ranking in 2020

The logic should work depending on the filter context. So when I analyze the values for 2021, the ranking may change, such as for Green and Orange:

Changed ranking in 2021
Changed ranking in 2021

Based on the top products of all color categories, I would now like to create a measure. For example, the average sales of these top products.

Creation of a virtual table with relevant measure

How is it now possible that we always filter on the rows with rank 1 in a measure? A measure like our ranking measure always needs a filter context. The answer is relatively simple, we need to manually build our filter context within the Measure using virtual tables.

To better understand the intermediate steps of the development, we will develop the measure in the DAX Studio. To do this, we first take a look at the Products table using the EVALUATE function.

Display product table
Display product table

To this table we would like to add our ranking in the next step. We can do this with the ADDCOLUMNS function:

Adding the ranking to the virtual table
Adding the ranking to the virtual table

We can save this virtual table in a variable, so that the code is easier to read. With the DEFINE VAR command we can create a variable called vRankTable. To this variable we assign the virtual table. We return the table at the end with the EVALUATE command. This one still looks identical:

Adding the ranking with ADDCOLUMNS function
Adding the ranking with ADDCOLUMNS function

Now we want to filter the table down to the products that have rank 1 in their color category. For this we use the FILTER function and filter on rank 1. As table to be filtered we take our virtual table just stored in the variable:

Filtering of the virtual table
Filtering of the virtual table

We can also save this filtered table in a variable for a better overview:

Save the filtered table as a variable
Save the filtered table as a variable

Using the filtered table as a basis for CALCULATE

In our case, we have now filtered the product table to the top products in the current filter context. Now the question is how to integrate this into CALCULATE. After all CALCULATE does not need a table as a parameter, it just needs some filter criteria, right? The answer is simple and possibly surprising, but tables arefilters in Power BI!

This sounds counterintuitive at first, since you often use functions like FILTER or REMOVEFILTERS to modify the filter context. But basically, most functions simply return tables that override the filter context of the current measure. I recommend that you take a look at the topic of Expanded Tables. This helps enormously in the understanding of DAX.

However, Expanded Tables would be a topic for a whole series of articles, so I won’t go into the basics now, but instead simply how to use tables as filters.

In our example, this means that we can simply use the virtual table as a filter for CALCULATE. Since CALCULATE returns a single value, but we need to return a table in DAX Studio. We can return the CALCULATE with curly braces as a table in fast notation:

Using the virtual table as a filter for CALCULATE
Using the virtual table as a filter for CALCULATE

As an example, our top products achieve an average sale of 2,833.33 USD. If we compare this to the general average of all products, the general is slightly below:

Result without further filtering
Result without further filtering

Integration in new measure

In the end, we still have to convert the code we just developed into a Power BI measure. For this purpose, the DEFINE can be replaced by the measure name and the EVALUATE can be replaced by a RETURN. We don’t need the curly braces anymore, because the measure now has to return a single value and not a table like in DAX Studio:

Average Top Products = 
VAR vRankTable =
    ADDCOLUMNS (
        Products,
        "@Rank", [Rank Color]
    )
VAR vFilteredTable =
    FILTER (
        vRankTable,
        [@Rank] = 1
    )
RETURN
    CALCULATE (
        AVERAGE ( Sales[Amount] ) ,
        vFilteredTable
    )

As a result, we can use all kinds of functions in our CALCULATE. In our case, for example, an average of sales of the top product in each color category. And the measure completely dynamically takes into account any filter context:

Display of top products depending on filter context
Display of top products depending on filter context
Denis Selimovic

Denis Selimovic

As a user from the very beginning, Denis Selimovic is passionate about Power BI and everything related to it. In his blog WhatTheFact.bi, he writes about the latest developments in Power BI and provides tips and tricks on the subject. Besides being a Power BI enthusiast, blog author, speaker and senior consultant, he is also co-organizer of the Meetup Power BI User Group Switzerland.

Kommentare:

0 0 votes
Article Rating
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x
%d bloggers like this: