Power BI – What If parameters

The latest release of Power BI Desktop (August 2017) contains a pretty cool feature that was teased back in the keynote of the Microsoft Data Insights Summit which is the ability to add What If parameters to your data model.

To add a What If parameter, just click New Parameter on the the Modeling tab

which brings up the following dialog

Here you get the opportunity to configure your new parameter including parameter name, Data type (whole number, decimal or fixed decimal) and values  such as the lower and upper bound, the increment and finally the default value.  The dialog also offers the option to add a slicer to the current page using the new parameter.

You can add more parameters so long as you pick a new name each time.

So what is actually happening under the covers?  What’s happening to your data model when you create a parameter?

Not much really, it’s a pretty simple implementation.  When you click the OK button, Power BI creates a new calculated table using the following new DAX function

GENERATESERIES(StartValue, EndValue, IncrementValue)

which now provides DAX the ability to generate a single column table of numbers other than using the CALENDAR function.  It’s slightly better than the CALENDAR function because it allows you to select an increment value which can be in decimal numbers (eg. 0.1 or 0.02 etc).  Increments can’t be Zero or negative.

The other item added to the data model is a calculated measure called Parameter Value as shown

This measure is the driver behind the slicer that is added to the report canvas if the option to add a slicer is selected.  If a default value is specified in the configuration dialog, this is simply added as a 2nd optional parameter to the recently added SELECTEDVALUE function.

All that is required now is to modify your existing DAX measures to take advantage of the new parameter.

So

Sum of Revenue = SUM('Sales'[Revenue])

could become

Sum of Revenue = SUM('Sales'[Revenue]) + [New Parameter]

which would allow someone using the report to move a slide based slicer and see the effect on the visuals.  I’m sure usage will be more sophisticated than this example, but shows how it can be incorporated.

And that’s it!

No real magic apart from a handy new GENERATESERIES function, which I know will find itself in plenty of scenarios other than this one.  This is a good thing as sometimes the simple solutions are often the best.

This is nothing we haven’t already been able to do with a bit of DAX knowledge but it sure makes it easier to implement and will no doubt appeal to plenty.  Like the Quick Measures feature, the output is a DAX based table and measure which you can edit and tweak to suit your needs.

It might be nice to get a similar wizard to produce a table of measures allowing users the quick fire ability to use a bunch of existing measures on the same vlsual.

Philip Seamark

Phil is Microsoft Data Platform MVP and an experienced database and business intelligence (BI) professional with a deep knowledge of the Microsoft B.I. stack along with extensive knowledge of data warehouse (DW) methodologies and enterprise data modelling. He has 25+ years experience in this field and an active member of Power BI community.