Power BI Intermediate Level: 51 - CALCULATE Mastery - Using Parameter Tables and Toggling Relationships

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

Short summary: You can use CALCULATE to filter via an unrelated table containing parameters. For example, you can have a user select a discount through a slicer and have that discount applied in a measure. Furthermore, you can use the USERELATIONSHIP function within CALCULATE to toggle table relationships for the calculation.

You have already seen many ways for using CALCULATE to change the calculation of measures by changing the filter context. Here I will show you two more useful ways to add to your Power BI skills.

Using Parameters from an Unrelated Table

Let's assume we want the user to be able to interactively select a discount and have that discount dynamically reduce the price. First we need a parameter table to contain the discounts. We can create one either in Power Query or in DAX. Let's create one in DAX using the New parameter wizard: Under the Modeling ribbon, click New parameter. Let's select the range 0% to 50% in increments of 10%. Also check the box for adding a slicer to this page.

Power BI will create a Calculated DAX table for us. Note the calculator symbol to the left of the table name. Unlike tables generated in Power Query, DAX table are generated using DAX functions. Except for that, there is nothing special about this table. In the Table view, you can see the DAX function which was used:

51_DIM_Discounts = GENERATESERIES(0, 0.5, 0.1)        

Power BI also added a measure for returning the value which the user selected. The default is 0%.

51_DIM_Discounts Value = SELECTEDVALUE('51_DIM_Discounts'[51_DIM_Discounts], 0)        

Change the number formatting of the column and the measure to percentage.

Change the slicer setting to style Tile and enable Single select.

Let's create the measure for the sum of sales with discount, using the measure which returns the single value which the user selected. This is important we can only multiply by a single value, not a list or a table.

51 Sum of Sales with Discount = 
CALCULATE ( [48 Sum of Sales] * ( 1 - [51_DIM_Discounts Value] ) )        

Let's plot this measure again the normal sum of sales measure and see the effect of the user selecting different discounts:

Building a Cumulative Time-Based Measure

You can also use this pattern for filtering within CALCULATE. Suppose we want to cumulative sales from the very beginning up to the date on the x-axis. Let's use the pattern of intercepting and modifying report filters. First remove all filters from the calendar table, then filter the date column by the latest date coming from the report filter context. For example, for June 2022 in the x-axis, the latest date would be June 30, 2022.

51 Sum of Sales with Discount Cumulative = 
CALCULATE (
    [51 Sum of Sales with Discount],
    ALL ( '48_DIM_Calendar' ),
    '48_DIM_Calendar'[Date] <= MAX ( '48_DIM_Calendar'[Date] )
)        

Below you can see the cumulative measure in action. Since we don't have data for year 2023, the cumulative sales stays constant there. To hide the year 2023, we could set the filter on the visual or wrap the measure in an IF statement to return BLANK for out-of-range dates.

Toggling Relationships with the USERELATIONSHIP Function

Within CALCULATE, you can toggle between different table relationships, which can come in quite handy. To demonstrate, let's look at some new sample sales data. The important difference is that we have two dates columns, one for purchase date and one for delivery date. Assume that we want to plot the number of purchases and the number of deliveries over a shared time x-axis.

Let's first create the table relationships. Note that between two tables, you can only have one active table relationship at a time. This is the one that's used by default. You can have many more inactive relationships. In this case, let's make the relationship for Purchase Date the active (default) relationship and the relationship for Delivery Date the inactive one.

The measure for the number of purchases is straightforward, since we can use the active (default) relationship with the calendar table:

51 Number Purchases = COUNTROWS( '51_FACT_Sales_Data_Two_Date_Columns' )        

But for the number of deliveries, how do we tell Power BI to use the currently inactive relationship? This we can do with the USERELATIONSHIP function. You need to specify the two related columns, in this case the Date column from the calendar table and the Delivery Date column from the sales table.

51 Number Deliveries = 
CALCULATE (
    COUNTROWS ( '51_FACT_Sales_Data_Two_Date_Columns' ),
    USERELATIONSHIP ( '48_DIM_Calendar'[Date], '51_FACT_Sales_Data_Two_Date_Columns'[Delivery Date] )
)        

This allows you to plot the two measures over the same time axis using two different relationships.

Conclusion

You learned two major new ways to change the calculation of a measure:

1. Use values from a parameter table in the calculation or the filtering portion of CALCULATE, for example for dynamically applying a discount.
2. Toggle between different table relationships in case you have more than one date column and want to plot information over the same time axis.

Now you should have really comprehensive knowledge of using CALCULATE. I highly recommend you to practice and apply these concepts.

Next up, we will return to Power Query for importing a whole folder of files at once.

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

Next article: Importing and Appending a Folder of Files