Power BI Intermediate Level: 40 - CALCULATE Mastery - Overwriting Report Filters

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

Short summary: When you specify custom filter conditions for a measure wrapped inside a CALCULATE function, those custom filter conditions will overwrite any conflicting filter is coming from the report. Non-conflicting filters from the report still get applied.

Consider our sample sales data as shown below, shown in a Matrix visual and filtered to the years 2020 to 2022 using a slicer on years.

Measures with Custom Filter Conditions Using CALCULATE

In the previous article, you learned how to specify your own custom filter conditions for a measure using CALCULATE. We have displayed our custom measures as single values in card visuals. As an example, we defined the following measure for the sum of sales in 2022:

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

Custom Filter Conditions Using CALCULATE Overwrite Report Filters

Let's see this measure in action in a second Matrix visual.

You will notice that in the second visual, you get the same sales values for each year, specifically the values from year 2021. For the year column in the second Matrix visual, there are two conflicting filters:

1. The Year from the Matrix visual
2. The custom filter condition for Year as an argument to CALCULATE in our measure

Whenever there is a conflict between the filter context from the report and custom filter conditions within CALCULATE, the filter conditions within CALCULATE overwrite the conflicting report filter context.

Now take a look at the products in the rows. We didn't specify any custom filter condition for Product within CALCULATE. So for Product, the filter context from the Matrix visual is used.

Conclusion

You can use CALCULATE to selectively overwrite filter context from the report. Non-conflicting filters from the report still get applied. This gives you additional degrees of freedom in designing your dynamic calculations.

In the next article, let's take a step back to understand the process of how Power BI combines filtering and computation to dynamically provide the values in the visuals.

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

Next article:?CALCULATE Mastery - Understanding Dynamic Visuals