Downunder DAX is a blog about all things Power BI, but to date, all of the articles that have been posted have been Power Query related. That being the case, this week I decided to change tack and focus on a DAX topic, with a write up of a question I was asked during one of our In-House private training courses.
The question was looking at solving the problem of post-sales support costs that change price over time due to the rate being charged for the support.
Below is the question summarized, plus a strategy to solve it.
Download the Sample Workbook if you'd like to follow along.
The Question
Let’s assume the example:
Rate 0.7 valid before 01/07/2017
Rate 0.8 valid between 01/07/2017 – 3/08/2018
Rate 0.9 valid afterwards
Is it possible to create a helper table containing rate information and get DAX to choose the correct rate to be applied to a calculation based on the time period?
What about What-If?
If you have had some experience with solving DAX problems already, you might be thinking that we could solve this problem with a What-If parameter.
While the What-If parameter is excellent for introducing a dynamic value into a calculation, it is limited in that it will apply to the entire calculation. For example, if I was to create a Total Support Cost calculation as Total Support Cost = [Support Minutes] * [Rate], where Rate represents a What-If value, then as we change the Rate from 70c to 80c, that new rate will be applied to the entire data set, including the dates where the rate was 70c.
To solve this particular challenge we need a different approach.
DAX Variables to the Rescue
The easiest way to solve this problem is by breaking it into pieces and the trick to that is taking advantage of Variables in DAX.
Understanding and implementing variables is an intermediate level concept of DAX, so don’t worry if it feels a little over your head at this point, you can always come back to this later, and I promise to write an article about Variables that will link back to this post.
Creating the Helper Table
The first step in solving this problem is creating the helper table that is going to contain the Rate and Date information. To add the new RateChange table to my data model I will go to the Home ribbon and click the Enter Data button.
This will be a disconnected table in the Semantic Model.
Identifying the Current Filter Date
To identify which rate would be selected for any given date we will start a new measure designed to detect the Selected Rate.
The first Variable for the Selected Rate measure needs to identify the date of interest, and to achieve that we'll use SELECTEDVALUE.
Selected Rate =
VAR ThisDate = SELECTEDVALUE('Calendar'[Date])
RETURN
ThisDate
With the date now in scope, the next step requires we filter the RateChange table to return the appropriate Rate based on the date selected in ThisDate.
To understand the logic required for this step, we’re going to look at what sort of filters we need to apply to the RateChange table to “reshape” it for the calculation.
Understanding the Filtered Table
The second variable in our calculation is going to be a virtual table. It is not always easy to understand what a virtual table is going to look like, and thus we are going to use the Data View to help us understand what will happen inside our virtual table calculation.
In the Data View, go to the RateChange table. Click the down arrow on the Valid From column (1), run your mouse over the Date Filters (2) and select Custom Filter… (3).
The rate first changed when the date was July 1st, 2017, so in the Custom Filter wizard, set the filter to be is on or before the 9 March 2017.
This returns a single Valid From date, January 1, 2016.
Now update the custom filter to is on or before the 3/7/2017. This time the table returns 2 dates (rows).
This means we are going to have to use two steps in order to select the correct rate. The first step will be targeted to filtering the table, the second step will select the correct date value from the filtered table.
Filtered Table Variable
In the illustration above, we discovered that if the RateChange table is filtered on or before (<=) the 30th of June 2017, the table is left with a single row, but if it is filtered on or before the 2nd of August 2018 it will return two rows, and thus if it is filtered on or after (>=) the 3rd of August 2018, it will return three rows. Therefore, in our FilteredTable variable we need to apply the same “custom filter” logic to the table.
Simply apply a FILTER on the RateChange table where the Valid From date is <= ThisDate.
Selected Rate =
VAR ThisDate = 'Calendar'[Date]
VAR FilteredTable = FILTER(RateChange, RateChange[Valid From] <= ThisDate)
RETURN
ThisDate
Although we are now successfully filtering the table we cannot visualise it inside the measure because measures return scalar values, and this FilteredTable variable returns a table of values. This is why we stepped through the filtering examples in the data view above.
Selecting the Date to Use
Now that we are able to sense the current filter's date, and we have a virtual copy of our disconnected RateChange table that will be filtered based on the current filter date, the next step we need is to ensure we have just one date available to work with from our RateChange, so we can use this to select the correct rate to apply into our Total Cost of Support calculation.
For this we’ll take the largest Valid From value by Calculating the MAX Valid From date, filtered by the FilteredTable variable.
Selected Rate =
VAR ThisDate = SELECTEDVALUE('Calendar'[Date])
VAR FilteredTable = FILTER(RateChange, RateChange[Valid From] <= ThisDate)
VAR Date2Use = CALCULATE(MAX(RateChange[Valid From]), FilteredTable)
RETURN
Date2Use
Next, set up a Table visual on your report canvas, with Date and Selected Rate to test the result of the measure. In the table visual, we can confirm the measure is working correctly because the date being returned by Selected Rate switches on the 1st of July, 2017.
Identifying the Rate to Use
Lastly we need to select the rate based on our Date2Use. Again, we’ll use a SELECTEDVALUE for this.
Selected Rate =
VAR ThisDate = SELECTEDVALUE('Calendar'[Date])
VAR FilteredTable = FILTER(RateChange, RateChange[Valid From] <= ThisDate)
VAR Date2Use = CALCULATE(MAX(RateChange[Valid From]), FilteredTable)
VAR Rate2Use = CALCULATE(SELECTEDVALUE(RateChange[Rate]), RateChange[Valid From] = Date2Use)
RETURN
Rate2Use
In the Table visual we can see that the rate is switching from 70c to 80c on the 1/7/2017, and again on the 3/8/2018.
Problem with Filter Granularity
Now that we can identify the correct rate we need to apply to our calculation we can use the SelectedRate as the multiplier in our Total Cost of Service, however if we simply multiply the Support Minutes by the Selected Rate things don’t work quite the way we might expect.
Total Support Cost = [Support Minutes] * [Selected Rate]
Everything works at the day level of granularity fine:
But if I switch the Date for the Year and Month I get different behaviour:
What’s happening here?
Why are we getting blanks?
The issue is to do with the SELECTEDVALUE. Now that the visual is no longer looking at each day, each row of the table now representing a single month, there are multiple dates selected in the current filter, and multiple dates means the alternate value for the SELECTEDVALUE function is being returned. In other words, the calculation is now Support Minutes multiplied by BLANK.
In order for the calculation to work correctly we need to ensure the Support Minutes multiplied by the Selected Rate is calculated day by day, and then added together to give the solution at month or year granularity.
This means we’ll need to iterate over the Calendar table, one day (row) at a time, and calculate the Support Minutes multiplied by the Selected Rate for that day. Then we’ll need to sum these values together at the end to return the correct Total Support Cost for each month or year as required.
The key to enabling this iteration is an X function, and because we need to sum the values together at the end, we’ll use SUMX.
And now the formula works correctly:
Solving in a Single Calculation
This is great and sufficient for resolving the immediate need, but for completeness of the lesson, I’d like to evaluate the entire result as a single calculation rather than rely on the Selected Rate interim measure, but I can’t just add the Total Support Cost calculation as another variable in the formula because I’ll get the Blank issue again:
Total Support Cost 2 =
VAR ThisDate = SELECTEDVALUE('Calendar'[Date])
VAR FilteredTable = FILTER(RateChange, RateChange[Valid From] <= ThisDate)
VAR Date2Use = CALCULATE(MAX(RateChange[Valid From]), FilteredTable)
VAR Rate2Use = CALCULATE(SELECTEDVALUE(RateChange[Rate]), RateChange[Valid From] = Date2Use)
VAR CurrentRowSupport = SUMX('Calendar', [Support Minutes] * Rate2Use)
RETURN
CurrentRowSupport
This is because within a measure a variable actually behaves as a constant, and thus once a variable has been calculated and assigned it will not change. Hence, the SUMX isn’t yielding the desired iterative effect on Rate2Use, because ThisDate was already calculated based on multiple dates, and so BLANK is being passed to each subsequent Variable.
The solution is to wrap all of the variables inside SUMX. This then forces the variables to be calculated for each row of the Calendar, just like what was happening when we place the interim measure Selected Rate inside the SUMX:
I’d encourage you to step through the process of writing the DAX yourself, but if you want to copy the code:
Total Support Cost 2 =
SUMX('Calendar',
VAR ThisDate = 'Calendar'[Date]
VAR FilteredTable = FILTER(RateChange, RateChange[Valid From] <= ThisDate)
VAR Date2Use = CALCULATE(MAX(RateChange[Valid From]), FilteredTable)
VAR Rate2Use = CALCULATE(SELECTEDVALUE(RateChange[Rate]), RateChange[Valid From] = Date2Use)
VAR CurrentRowSupport = [Support Minutes] * Rate2Use
RETURN
CurrentRowSupport
)
Conclusion
While the What-If parameter is an excellent tool for introducing a user-defined dynamic value into a calculation of a Power BI report, it is limited in the sense that as the value changes, it is applied to the entire dataset. This is fine when you are looking to perform simple scenarios such as 'what-if sales increase by x%'. When there is a need to provide different values to the calculation based on a specific combination of filters, then the What-If parameter is no longer sufficient, and we need an alternate approach.
In the example above, I leverage the power of Variables, Disconnected Tables, an X-Function (iterating) and the FILTER function to feed a different rate into the Total Support Cost calculation based on the period of time that is on display in the report.
While the example above has been simplified for this article, the concepts covered by this technique can be applied to help solve more complex real-world challenges such as calculating the Total Sales value when the Product Price changes over time or across different geographical regions.