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.
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.