DAX parameters are similar to What-if analysis in Excel - they let you gather user defined input and use it within your report. Since they're DAX parameters, we're going to need to use a bit of DAX to make them work.
You can write the parameter yourself, but if you use the built in Parameter button in Power BI desktop, you'll automatically get the DAX table expression, measure, and single select slicer. So why not take the easy way?
Click the Modeling tab in the ribbon > New parameter.
I like to name my parameters something that is user friendly and instructive. In this example I'm trying to create a parameter for our Freediving report that enables you to adjust the depth of the diver, so I'm going to name my parameter "Select diver's depth".
The data type is a Whole Number.
Minimum 0; what's the smallest value your end user will want/need to do What-if analysis on?
Maximum 65; what's the largest value your end user will want/need to do What-if analysis on?
You may need to consider your data when setting the allowed values for your parameter - what does the dataset allow? What will make sense?
Increment 1; starting at the Minimum, how much should the What-if analysis increase each time?
Consider the granularity of your report and analysis - how much detail do you want/need?
Default 0; if the end user doesn't select a value for the What-if analysis, what default will we use for the calculations?
Note: I'll often set the Default to 1 for percentage change parameters and 0 for value increase parameters. Think about what you're using it for.
Once you click OK, a few things happen:
- A new table using DAX function GENERATESERIES is created with a single column. Both the table and the column are named the same as your parameter
- A new measure using DAX function SELECTEDVALUE is created with an alternate result equal to the Default you specified. The measure is named the same as your parameter with the word 'value' after it.
- A slicer is added to the page using the new measure. It defaults to the 'single value' option.
Note, this slicer does NOTHING until you do a bit more DAX legwork. Since we've added a new table to our data model, we should check the Model view - you'll notice there are no relationships on this new table. In some cases, a relationship may be all you need to get your parameter working properly, in many cases DAX is the magic that brings it all together.
I'll examine DAX parameters in another article. Today, we'll simply create a relationship between [Select Diver's Depth] and [Depth].
That's it! You've created a What-if parameter in Power BI.
