The techniques used here are seriously awesome. The opportunities are really unlimited once you understand the steps required to make this happen.
To confirm what I mean by making visuals dynamic, in this example what we want to be able to do is dynamically view information over different time periods like 7, 14, 30, 90, 180, 360 days and we want this to always update for the current date and then count backwards.
What's amazing here is that we also want to control what every visual shows in our report page so that it reflects the time frame we have selected.
On top of this I wanted to be able to see this for two different metrics, either revenue or profits.
I like to call this technique multi-threaded dynamic visuals
So think about that. In this one report page, we are able to view all our visuals across 6 different time frames but ALSO across different financial metrics.
Let's go through the steps on how you do this.
First off, we need to create the tables which will 'harvest' our selections. We need to create the slicers below.
You can do this a couple of ways but I have created this simply by using the 'enter data' option in Power BI desktop.
Now, we need to create two tables here. One for the date range, but also one for the financial metrics selection.
There's a couple of things to note with these tables.
For the date ranges table you need to make sure you have these three columns.
The time frame column is what will be used in our slicer, but the other two columns are equally as important. The days column is what we need to use in our calculation and the index column is there to make sure that the slicer is sorted correctly.
For the financial metrics table, you need to create something simple like the below.
Now we have these set up, remember that these tables have NO RELATIONSHIPS to any tables in the core data model.
These tables are supporting tables or parameter tables. We are going to use them to 'harvest' a selection or measure and feed that back into calculations we are doing in the real data model.
Okay, so now that we have the initial part set up we need to start capturing the selection being made on the slicers, so that regardless of the selection, ultimately the visuals will know what to showcase.
For an example of what I mean here check out this really quick video.
So how do we do this....well we need to 'harvest' the selection and we do this by using the VALUES function.
Check out the formula below which creates a measure which will hold the selection that is made. This is actually a really interesting DAX formula.
We first of all need to make sure only one slicer value is selected (we can only ever have one time range selected at any one time), we do this by using HASONEVALUE wrapped inside an IF statement.
That evaluates to TRUE or FALSE if there is one selection on the time frame slicer. If there is (TRUE) we use VALUES to capture the numeric days associated to the selection. If it is (FALSE) we default to the entire date range, which is done via the COUNTROWS( Dates ) formula.
With this formula we have created a measure that we can then use in another measure. This is what is called measure branching...using measures within other measures.
Now we have the amount of days for the selection, now we need to make a dynamic range which always goes backwards from TODAY.
This is how you do it.
The key thing to see here is the 'total days' at the end of the FILTER function. Remember that stems from our slicer selection. The the date range is being determined by whether we are selecting 7, 90, 360 days etc.
Think about this now. Our 'Total Revenue' measure is going to be different for each selection that we make in the slicer. We can use this measure just like any other measure and place it into a visual or table.
Now we actually have a bit of a pattern going on. You can re-use this quite easily with other metrics and that is exactly what we are going to do.
We've created our dynamic revenue number, but now we want to create a dynamic profit number.
It's simple from here. Just copy and paste this existing measure and then sub in the profits measure.
You could keep doing this for all your core measures if you really wanted to.
But now we need to create one MASTER MEASURE because if you think about it we only want to place one measure into all these visuals.
It's the same measure everywhere as you want the slicers to dictate, in this example, every visual on the page.
So somehow we need to join up the two dynamic financial metrics - 'total revenue' & 'total profits'. We need only one measure that dynamically changes for whichever selection is in the slicer.
You do this with the following technique.
The key here is the SWITCH function
Using the SWITCH function is like using nested IF statements. Through VALUES you can check which of 'Revenue' or 'Profits' is actually selected, and feed through a new calculated result by referencing a measure (and we are bringing through our dynamic measures that we created earlier).
This new measure is now our MASTER MEASURE. We are going to use this in every single visual.
Can you see how it all fits together now?
Let's do a quick recap for a multi threaded dynamic visuals.
1. We created the slicers for the date range and financial metrics. We used these selections to dynamically change the results in our visuals.
2. We then 'harvested' those selections inside a measure.
3. We used that measure to create dynamic financial metric measures - 'total revenue' & 'total profits'
4. Then we used the SWITCH function to create one master measure.
Once we've done all of this the hard work is done. We only then have to use one measure inside of each visual.
Think about the variety of looks we can now achieve inside our visuals. Not only do we have a variety of dimensions actually in our data model but we've brought it many more through the supporting tables used in this technique.
The visual possibilities have gone up by around a factor of 5. And what's stopping you there. You can get seriously advanced here and have multiples of selections available to you inside a report page. It's just how you set it up.
Good luck with this technique!
Sam McKay, CFA
Want to become a Power BI & DAX super user? Check out the Definitive Guide to Power BI
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.