Power BI Intermediate Level: 45 - CALCULATE Mastery - Intercepting and Modifying Report Filters

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

Short summary: Within CALCULATE, you can intercept and modify report filter context to suit your needs. In this article, I will show you this pattern with the example of calculating the sales of the previous year, and with that the year-over-year growth in sales.

Let us now look at the pattern where we intercept and modify a report filter context, with the example of calculating the previous year's sales.

Getting a Single Value using SELECTEDVALUE

Before we can do that, we need to learn a new aggregate function. Suppose you have a Matrix with Products on rows and want a measure which returns the Product if there is only one product filtered. The function that does this is called SELECTEDVALUE and it returns the only selected (filtered) value of a column or returns blank in case there is more than one filtered value. In our example, we can define our Measure like this:

45 Selected Product = 
SELECTEDVALUE ( '39_CALCULATE_for_Filtering_within_Formula'[Product] )        

Here you see the Selected product Measure in action, showing the single filtered product for each data point in the main part of the Matrix visual and in the row totals. The column totals are blank because these data points have multiple columns filtered so SELECTEDVALUE returns blank there.

Concatenating Multiple Text Values using CONCATENATEX

Note: If you wanted to instead concatenate the texts of multiple Product labels, you could use the CONCATENATEX function. I won't go into detail here but this is how you would do it, indicating the table, the column to concatenate, the delimiter, and the column by which to sort as well as the sort order:

45 Selected Products = 
CONCATENATEX (
    '39_CALCULATE_for_Filtering_within_Formula',
    '39_CALCULATE_for_Filtering_within_Formula'[Product],
    ", ",
    '39_CALCULATE_for_Filtering_within_Formula'[Product],
    ASC
)        

Calculating Previous Year's Sales

Now, let us return to the original problem of calculating the sales of the previous year. This is a bit of a tricky one. We need to capture the year filter and replace it by that year minus one. Let me first give you the correct formula and then walk you through the steps:

45 Sales Previous Year = 
VAR Current_Year_From_Report =
     SELECTEDVALUE ( '39_CALCULATE_for_Filtering_within_Formula'[Year] )
VAR Previous_Year =
     Current_Year_From_Report - 1
VAR Output = 
     CALCULATE (
         [39 Sum of Sales],
         '39_CALCULATE_for_Filtering_within_Formula'[Year] = Previous_Year
     )
RETURN Output        

For the marked green example data point (Year 2022 and Product C), the filtering is shown below:

Let's now walk through the steps: We can get the year from the report filter context, i.e. from each data point in our visual, like this:

VAR Current_Year_From_Report =
     SELECTEDVALUE ( '39_CALCULATE_for_Filtering_within_Formula'[Year] )        

For the marked example data point, this yields the year 2022. Then we subtract one year:

VAR Previous_Year =
     Current_Year_From_Report - 1        

For the marked example data point, this yields the year 2021.

Finally, we overwrite the year filter context as follows:

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

Let's note a few things:

• The product filter remains unchanged.
• If there were no previous year's sales, we would get blank values
• For the row totals, we get a blank results because there we would have multiple year values and our function SELECTEDVALUE can return only one value.

Calculating Year Over Year Sales Growth

We can now calculate the Year over Year sales growth using the DIVIDE function:

45 Sales Year Over Year Growth = 
DIVIDE (
    [39 Sum of Sales] - [45 Sales Previous Year],
    [45 Sales Previous Year]
)        

Performing Time Intelligence in Power BI

Now that you understand this concept, you may be glad to hear that there are time intelligence functions such as SAMEPERIODLASTYEAR, which facilitate the interception and modification of time filter context in a smart way. In order to use these functions, you need a column of type date for input and optimally use a calendar table with a consecutive date range and a variety of ways to filter for dates, e.g. years, quarters, months, calendar weeks, etc. More on that in a later article.

Conclusion

You now know one of the most powerful techniques for using CALCULATE: Intercepting and modifying report filter context get relative values or percentage figures.

Next, I will show you how to create calculated columns in DAX where you can leverage relationships.

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

Next article: Calculated Columns in DAX - Basics

?