The Power BI Desktop April update contains several improvements, including templates, conditional columns and formatting tables but for me the most important are parameters since they make possible useful ways of interacting with Power BI reports.
For example, risk managers like to compare the VaR, a measure of the risk of a portfolios between any two dates they choose. Let’s call these two dates the “Start Date” and the “End Date”. This is not possible by filtering the data – if we first filter all data by the Start Date, we have already filtered out the data for the End Date.
However, we can achieve this with parameters in the three easy steps. Firstly In the Query Editor, set up parameter Start Date with Date data type.
Create an ‘End Date’ parameter also.
Secondly create a new measure “VaR At Start Date” with the DAX below.
Note that it references the “Start Date” Parameter. The MAX() is necessary since DAX has to be sure it is a single value. Of course our parameter contains only one value so we can safely do that. Create a ‘VaR at End Date’ measure in the same way.
Finally add those measures as values well to a side-by-side bar chart and use another dimension on the ‘Axis’ well.
The icing on the cake is to create the parametrised title for the chart by creating a DAX measure below that includes the Start Date and End Date parameters.
Chris Webb shows an advanced use of this technique in his blog.
Now users can change the parameters and the values on the chart refresh accordingly.
This first version of the parameters is very useful but there are clear ways that future versions may improve on this.
- An obvious improvement would be to have a calendar picker for dates.
- Also, at the moment we can only restrict values of our parameter to a list of allowed values – not helpful if your data contains 2 years of dates. It would be useful to set a range.
- Furthermore we’d like to set a range of one parameter based on the value chosen by the user of another parameter – so that our End Date can never be earlier than our Start Date for instance.
- When the user changes parameters, PBI desktop refreshes the data source. This is problematic if the data refresh takes more than few seconds.
- In our example, changing the parameters does not actually require a data refresh.
- Finally, parameters are available in the desktop but not yet in the service.
Having said all that, parameters do represent a big step forward in improving the usability of Power BI.