Use 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:

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:

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:

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.

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

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:

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:

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

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:

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:

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:

Download sample file