An Introduction to Parameters in Power BI - Part 1: What If Parameters
Nick Hilton | 01 September 22 | 4 min read
Hello and welcome back to Infused with Data. So, with this blog post I've really pushed the concept of using it as a way to learn which has been really helpful to me. I think this is definitely a topic which is underutilised and is lacking some helpful easy introductions to it like this is, so I hope you find it helpful too! Let us introduce you to Parameters in Power BI, and be sure to come back in two weeks for part 2!
Welcome to the first of a two-part post on parameters in Power BI. The first will be on the what-if modelling parameter that exists in the reporting layer and the second will focus on the super powerful parameter function in Power Query. To be honest, this is one area that until this point I haven’t explored much, and that’s because while I understand them it was never enough to identify opportunities where they could be used to really drive that reporting value to the next level. Having now taken that time to learn how they work I can’t wait to use them, and hopefully by the end of this post you’ll be able to identify some areas where they could help you too.
What-if Parameters?
It’s easy to forget just how interactive Power BI can be, most of the time we’re looking at the data as it’s presented to us, using some basic slicers, filters, and then clicking to further cross filter or highlight certain data points. But what if (see what I did there?) there was a feature where users can simply but dynamically forecast changes and see the potential outcome of different scenarios? Well, there is and the best part is that it’s actually pretty simple to set up.
A What If Parameter is mainly used for the visualization and analysis of the effects of a change in a variable. For example, how much would my sales need to increase so that each month we achieved a certain quota. I think it can also be used as a way to make certain complex filtering scenarios much more straight forwards for your end users. For example, only show sales for transactions where the amount was under ‘x’ where x is a figure on a slider that can be adjusted with a click. Below is a live example of a report you can check out to see what I mean, the first page being the sales forecasting example and the second showing off the analysis scenario.
I also highly recommend checking out this video from ‘How To Power BI’ which is a great channel to learn from. His use of adjustable forecasting is a fantastic example of the potential of these parameters.
How to use What-If Parameters
There are some excellent resources from people who have used this in the real world so I recommend checking out the links at the bottom of this page for a full tutorial but I want to give you a very quick overview of what’s involved so you can judge whether this is useful for you at this stage on your BI journey.
Creating
1. Select New Parameter from the Modelling tab in Power BI Desktop
2. Create your range of numbers and increment by filling out the details. E.g. if you wanted to forecast a revenue multiplication from -10 to 10 then you’d do decimal as data type -10 as the min, 10 as the max and the preferred increment.
3. Don’t worry about the format that can be changed later
4. Click okay and (assuming you left the option selected) a slicer will be added to the page with your parameter scale included
5. Your parameter exists as a calculated table which shows up in the fields pane
6. This is where things become different depending on what you’re trying to achieve but let’s say the scenario is to add that second bar which shows revenue multiplied by your forecasts
7. Create a measure which multiplies revenue by your parameter value, it will come up as you type in the DAX
8. Add that measure to your visual
9. Voila!
Resources
Please do check out some of the below resources, this a fantastic but underutilised feature which if used correctly could really push up the value your users are getting from your report.
How To Power BI – YouTube
Guy in a Cube - YouTube
