Sometimes it is the simplest ideas that can add value – I think these ideas are two good examples of that.
I was working with a client this week helping them with their Power BI project. We are building tools to analyse General Ledger data that we have made available in Power BI. It is very common with GL data to have multiple versions (or scenarios) of the data loaded into the fact table (eg budget, actual, forecast as at Q2, forecast as at Q3 etc). It is therefore essential that the data is first “filtered” by one of the scenarios at all times – it makes no sense to sum up the values in multiple scenarios at any time.
I have created a simple sales forecasting scenario workbook below where I have several different versions of the data loaded in my fact table (a budget and 2 different versions of a forecast). See how in the image below the numbers on the right are an aggregation of multiple scenarios – these numbers have no meaning at all to anyone and the data should never be viewed this way.
It therefore follows that you must always select a single scenario when look at this data. The default slicer setting in Power BI is a ”single select” slicer (shown below) however it is still possible for a user to multi select by holding control on the keyboard and selecting more than 1 scenario.
More on the trick to how to prevent that later
Think Outside your Old Paradigms when using Power BI
I am a big believer in finding ways to add value to the visualisations you build by leveraging the features and capabilities that are available. If you just try to replicate what you have always done in Excel, you will be missing out on opportunities to add additional value with the Power BI tools.
Compare the 2 different approaches to slicing data below. The one on the left is using a standard Power BI slicer and this works just fine – just like it would with a Pivot Table in Excel using a slicer.
The example on the right adds more value over the one on the left. The example on the right uses a column chart instead of a slicer. The benefit of this is you can communicate more information to the user than you can with the static slicer. In this case I am displaying the total value of each of the scenarios which means it is easy to see the relative difference between them. And this all happens while still providing slicer capabilities thanks to the powerful cross filter feature in Power BI. This is what I mean by adding value by thinking outside your old paradigms.
How to Prevent Confusion with the Users
Now there is a new issue that has been introduced by the “added value” column chart slicer shown above. The problem is that slicers can be published to Power BI with a single slicer value selected and single select turned on. It is not possible to publish a column chart with one column pre-selected.
So do you see the problem here? By “adding value” to the user and swapping out the boring slicer in the report on the left with a value adding column chart on the right, it could create confusion by aggregating all the scenarios into a non-sensical hash total.
IFHASONEVALUE and Cards to the Rescue
To solve this problem, I created a warning message to display to the user. This warning message is set to display when ever there is more than 1 scenario selected – it disappears when the user selects a single scenario. The DAX for this is really quite easy.
Warning Message= IF(HASONEVALUE(Scenario[ID])," ","You must select a single scenario by clicking the column chart at the top of the page")
You can see how it works below – this is a live embedded interactive report direct from Power BI using the Publish to Web feature. Click on any single column in the chart to hide the warning message, and click on the same column again to make the message reappear.
I did a couple of things to make this work the way I wanted. I sent the warning message behind the other visuals using “send to back”. This is important to prevent the user accidentally selecting the message. Also note that I used a space “ “ instead of BLANK() as the alternate result. If you use BLANK(), then Power BI will return the word BLANK and that is not what is needed here. By the way, please vote for this idea to change this behaviour where it returns the word BLANK.
And of course this warning message is useful even if you use a regular slicer, to warn a user in case they multi select a regular slicer.