Utilizing the WHAT-IF Parameter in Power BI

Utilizing the WHAT-IF Parameter in Power BI Power BI, in addition to creating standard reports, excels in building various types of calculators. To handle variable values defined by users, we use the so-called WHAT-IF parameter (what if?). Creating it is straightforward; you can find it on the Modelling ribbon.

Upon clicking “New parameter,” a context menu will appear, allowing you to configure the new parameter (as shown in the screenshot below). You only need to define its name, data type, boundary values, and interval. You can predefine the default value or leave it empty. After selecting “add slicer to this page” and confirming, a slicer with an input field and slider will be added to your report, allowing you to define the value of the new parameter.

Additionally, in your model, a table named after the parameter will be created with a single column having the same name as the parameter. There will also be a measure with the same name suffixed with “Value.” The “parameter value” measure is nothing more than the reading of the specified parameter.

Now, let’s move on to an example of application (as shown in the screenshot below). I created the “Target” parameter to determine the sales target and added the “Target Value” measure to a combo line and column chart. From now on, whenever I change the target parameter value (current value: 7M), the line on the chart will shift up or down accordingly.

Apart from the chart, I also created a table with dates and sales values, along with an additional measure, “Target check.”

The “Target_check” measure is based on a logical expression that checks whether a single value from the calendar has been chosen. If yes, it evaluates an expression that checks if the sales value is higher than the specified target. If the expression is true, it returns “Passes target,” otherwise “Fails target.” If the expression does not have a single date from the calendar, it returns a blank value. This last step is used to avoid evaluating the sales sum for the specified period (in this case, selected as 7 months) unnecessarily.

Target_check = IF( HASONEVALUE('Calendar'[Date_YYYYMM]), IF( [_CurrentSales] > Target[Target Value], "Passes target", "Fails target" ), BLANK() )
This is just a simple example of using the WHAT-IF parameter. I encourage you to try it on more complex scenarios, such as nesting in calculations when creating different calculators or forecasts.

Interesting article? Share further!

Leave A Comment