Power BI Intermediate Level: 44 - CALCULATE Mastery - Constraining Report Filters

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

Short summary: If you want to constrain report filters rather than replacing them, you can use KEEPFITLERS as an argument to CALCULATE. This way, both the report filter and the filter argument within KEEPFILTERS are combined using an AND condition. A common application of this is to add some constraints to a report filter.

In the previous few articles, you learned how to replace or remove report filters within a measure using CALCULATE. In this article, I will show you how to constrain a report filter using CALCULATE and KEEPFILTERS. In my experience this is a rather rare scenario but still good to know.

Let’s assume we want a Measure which gives us the sum of sales since year 2021.

Wrong Solution

For better understanding, let me first show you the wrong solution. If we overwrite the filter context for the Year column coming from the report, like we have done before, we get the total over years for every data point, as shown below.

44 Sum of Sales since 2021 WRONG = 
VAR start_year = 2021
VAR out =
    CALCULATE (
        [39 Sum of Sales],
        '39_CALCULATE_for_Filtering_within_Formula'[Year] >= start_year
    )
RETURN
    out        

Correct Solution

What we need to do instead is to add a constraint to the report filter context. We can do this by wrapping our filter condition '39_CALCULATE_for_Filtering_within_Formula'[Year] >= start_year within a KEEPFILTERS function. This way, the intersect of report filter context and the argument within KEEPFILTERS gets evaluated using an AND condition.

44 Sum of Sales since 2021 = 
VAR start_year = 2021
VAR out =
    CALCULATE (
        [39 Sum of Sales],
        KEEPFILTERS ( '39_CALCULATE_for_Filtering_within_Formula'[Year] >= start_year )
    )
RETURN
    out        

The measure correctly returns values for only years 2021 and onwards and otherwise keeps the report filter context. Other columns, such as the Product column, are unaffected.

A couple of notes:

• In most cases, you can achieve the same result with a simple SUM measure and using a report filter on years, so that only the years of interest get displayed
• In some scenarios however, you may need to use this technique, especially when you need to reuse this measure in other measures
• We are using a fixed variable for the start year here. If you want to generalize this to, for example, within the past three years (this year and two years before), you can set the variable start_year as follows:

VAR start_year = YEAR(TODAY()) - 2        

Conclusion

You know know how to selectively apply a restraint on a report filter using CALCULATE and KEEPFILTERS. This has rather rare applications but it is still good to know and should further aid your understanding in how you can manipulate filter context.

In the next article, I will show you a very powerful technique for arbitrarily modifying report filter context, for example to return for each year the sales of the previous year.

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

Next article: CALCULATE Mastery - Intercepting and Modifying Report Filters