I have written about both types of parameters in Power BI; Power Query parameters and also the What If parameters. However, still many people are not aware that these two are different, and serve different purposes. In this blog article, I am explaining the differences between these two types of parameters, as well as the use case scenarios for each. If you want to learn more about Power BI, read Power BI book from Rookie to Rock Star.
Power Query Parameters
Power Query parameters can be created through Power Query Editor
However, their value can be changed even through Power BI Desktop (or Power BI Service)
What If Parameters
What If Parameters can be created only through Power BI Desktop, using the Modeling Tab
Values of What If parameters can be changed using the user selection of the slicer value
Power Query Parameters Need Refresh Data
To see the result of a change in a Power Query parameter, you need to Refresh the data or Apply Changes (which actually runs the refresh data). Without refreshing the data, you won’t get the change in the parameter value applied on the dataset.
What If Parameters Are Dynamic. No Refresh Needed
What If parameters, on the other hand, are dynamic. The user doesn’t need to refresh the data after making changes. After changing the value, immediately the effect applies.
Power Query Parameters are Suitable for Changes in the Data Transformation Layer
If you want to apply any changes dynamically in the data transformation layer, then Power Query parameters are the answer. Here are example use cases of using parameters;
Power Query parameter to change the server address of a database server from Dev to Test, or to Prod
Power Query parameters for creating a custom function and looping through a number of steps for different data sources with the same data structure
Here are a few more scenarios that you can easily achieve using the Power Query parameters:
- Changing the database name, the Excel file path, or address of the data source
- Changing table names, Excel sheets, etc
- Changing a set of transformations using a value coming from another table
- or any other things in Power Query that you want to be dynamic.
What If parameters are for User Interaction
What If parameters, on the other hand, are useful for the user interaction. You can easily create a parameter that changes a value in a DAX expression using What If parameters.
Sales of X Months ago using What If Parameters
Here are other scenarios of What if Parameters use cases:
- Change a DAX measure calculation based on the selection of the user.
- calculate rolling 12 months sales, rolling 6 months, rolling 2 months etc
- changing the selected measure visualized in the visualization using a slicer
- or changing anything in DAX measure expression dynamically by the end-user.
Not Recommended Use Cases
It is recommended to use Power Query parameters for making the data transformation dynamic, and What if parameter, to make DAX expression dynamic. It is not recommended to do it the other way around. here are some bad practices:
- Using What If parameters to change the server address from Dev to Test or to Prod. Because that means you need to load the entire data first into Power BI for all the environments, then apply what if parameters on it. This is something you need to do with Power Query parameters.
- Using What if parameters to change the source Excel file or folder. Exactly similar to the above example.
- Using Power Query Parameters for user interaction. Because after changing the value, the user need to refresh the dataset, and often they don’t have access to do this action.
Two Different Types of Parameters, Two different Purposes
Bottom line is that there is no BETTER type of parameter here. I use both these two types of parameters in every Power BI solution.
Power Query parameters are for applying changes in the data source or data transformation dynamically, What If parameters are for user interaction to change a calculation dynamically.
You cannot use one instead of the other. It is like saying you would build a house with only roof, and no walls, or only with walls and no roof. Neither is possible. You need both to build a complete dynamic solution.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
Is there any possible to publish What if parameter report to Web?? could please tell me how??
Not sure what do you mean.
Do you mean if you use a what-if parameter in a report, then can you publish it to the service? yes, of course.
Cheers
Reza
Hello Reza,
My customer wants me to create “What if Parameter ” in Power BI service without using Power BI Desktop?? is that possible could you please tell me how can we do it.
Thanks In Advance,
Ahsiq
modeling changes in a PBIX file cannot be done directly through service, things such as creating relationships, writing measures, and what-if parameter can be only developed only in PBI Desktop at the moment, and the result to be published to the service. I don’t understand what is your customer’s problem with Power BI Desktop?! The desktop is the place to develop models, not the service.
Cheers
Reza
Hello Reza,
I created a “What if parameter” to increase or decrease the total final price of a forecast quantity. It works exactly how I was picturing it but I have been asked if would it be possible that the number selected in the parameter will only affect a product group (or set of product group) based on what I select in a slicer.
For instance:
Total sales = 100
Product A = 30
Product B = 50
Product C = 20
If I increment 10 % (and nothing is selected on the slicer) would be:
Total sales = 110
Product A = 33
Product B = 55
Product C = 22
And now if I select on my slicer only Product A then the parameter of +10% just modify Product A but not the others:
Total sales = 103
Product A = 33
Product B = 50
Product C = 20
I am not sure if this makes sense or not but I would really appreciate some help on this 🙂
Thanks a lot,
Cheers.
Hi Alan
You can add an IF inside your sales expression that checks the slicer selection (you can do that using SELECTEDVALUE or VALUES etc), and then based on that result change the value for the specific product category.
Cheers
Reza
Hi
Is there a way to use this what-if parameter to change the query string of a web source that returns json data? if not what is the best way to accomplish that? I am trying to let user change some variables in powerbi service and get some calculations from a web service.
BR,
You can still use the Power Query parameter for that purpose, but then the user has to go to dataset settings, and set the parameters there, and then refresh the Power BI report.
What is the difference between Power BI Parameter and Row Level Security?
There are two different things for two different purposes:
Row-level security is for filtering the data so that each group of users see a different view of the data rows
parameters are to give the users the ability to change values (and based on that some calculations) dynamically
Hi, I set my what-if parameter slicer as type-in instead of using slide or drop-down. When I enter the value it doesn’t show the exact value few times and it shows the value less than one increment value. For example, if I create the what if parameter as GENERATESERIES(0, 50000, 1), and I type the value as 42400, then it shows 42399 instead of 42400. Can you please explain why exact value not shown up?
Hi Mohan
GENERATESERIES function starts with the value of the first parameter. Meaning that GENERATESeries(0,10) will be numbers from 0 to 9, but GENERATESeries(1,10) will be numbers from 1 to 10.
you can learn more about generateseries function in my article here: https://radacad.com/creating-a-list-of-numbers-or-dates-in-power-bi-using-generateseries-function-in-dax