Power BI Intermediate Level: 42 - CALCULATE Mastery - Dynamic Filtering within a Measure

Table of Contents?|?Power BI Report File?| Sample Input

Short summary: Within CALCULATE, you can specify a filtered table as an argument to apply filtering. You usually specify the filtered table as a result of a function which returns a table. One such table function is FILTER, which allows for filtering with dynamic results. Another useful table function is ALL, which returns all rows of a table, i.e. removes any report filters.

In the previous article, we established that Power BI calculates each data point in a visual independently. You have also learned in previous articles that you can overwrite report filters by wrapping a measure in CALCULATE and specifying filter conditions like below:

39 Sum of Sales in 2022 = 
CALCULATE(
    [39 Sum of Sales],
    '39_CALCULATE_for_Filtering_within_Formula'[Year] = 2022
)        

Note that this only works for static comparisons, like the fixed number 2022 in the above case. In this article, I will show you how you can filter dynamically.

Use case

In our sales example, let us create a measure for the percentage of records which have sales above the grand total sales average over all years and products. This measure needs to work for every data point in our visual, including totals. Let us segregate the building of the measure into steps.

1. Calculate the grand total sales average
2. Calculate the count of rows
3. Calculate the count of rows where Sales is greater than the grand total sales average, as calculated in step 1
4. Divide the result of step 2 by the result of step 3

1. Calculate the Grand Total Sales Average

If we use a simple average measure, we get the grand total average of €25,730.89 over all products and years in the grand total cell.

However, we need this number in every cell, because we want to compare to that grand total in every cell. For this, we need to remove all report filters. We use the following measure:

42 Grand Total Average of Sales = 
CALCULATE(
    [41 Average of Sales],
    ALL('39_CALCULATE_for_Filtering_within_Formula')
)        

Note that we are reusing the measure 41 Average of Sales, which we defined in the previous article. It is usually a good idea to reuse measure in this way to avoid duplicate code and to allow for later editing in just one place, rather than in many places.

The ALL function when applied to a table returns all rows from the table, i.e. removes all report filters from the table. With that measure, we get the grand total average in every cell.

2. Calculate the Count of Rows

This one is easy, let us calculate the count of rows without changing any report filters. If we don't change any filters, we don't need to wrap the measure in CALCULATE. However, we should preempt an issue we would face later when we reuse the measure in step 3: When there are no matching rows, the measure would return BLANK, instead we want it to return 0 in that case. Let's use the COALESE function, which replaces BLANK results with whatever we specify as a second argument, in our case the number 0.

42 Count of Sales = 
COALESCE(
    COUNTROWS( '39_CALCULATE_for_Filtering_within_Formula' ),
    0
)        

3. Calculate the Count of Rows where Sales is Greater than Grand Total Average

Let's now calculate the count of rows where Sales is greater than the grand total sales average, which we calculated in step 1. If we to try to use a simple filter conditions in CALCULATE, we get an error.

Instead, we need to use the FILTER function, which can handle dynamic fields. The filter function has two arguments:

1. The table which is to be filtered, in this case the table 39_CALCULATE_for_Filtering_within_Formula
2. The filter conditions, in this case for each row we check if the value in column Sales is greater than the value return by the measure [42 Grand Total Average of Sales]

Using the FILTER function, let's write our measure. Note that you can use autocomplete to help you write the table, column, and measure names.

42 Count of Sales Grater Grant Total Average = 
CALCULATE(
    [42 Count of Sales],
    FILTER(
        '39_CALCULATE_for_Filtering_within_Formula',
        '39_CALCULATE_for_Filtering_within_Formula'[Sales] > [42 Grand Total Average of Sales]
    )
)        

4. Divide the Results of Steps 2 and 3

Let's divide the measure for steps 2 and 3 to get our final result.

42 Ratio Sales Greater Than Grand Total Average = 
DIVIDE(
    [42 Count of Sales Grater Grand Total Average],
    [42 Count of Sales]
)        

Instead of reusing measures, you could have instead written a single measure using variables and a return statement. You can refer to the Power BI report file where I also implemented this measure using variables.

Conclusion

You now know of to overwrite existing report filter context using filter conditions with static or dynamic variables. This allows you to write complex, powerful measures, like we have done here.

In the next article, I will show you how to selectively remove report filter context to calculate totals and percentages of totals.

Please like, share, and subscribe and feel free to ask questions in the comments below.

Next article: CALCULATE Mastery - Removing Report Filters