Power Query Parameters Vs. What If Parameters: Power BI Implementation Use Cases

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

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

2016-12-06_17h51_22

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.

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
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.

Leave a Reply

Your email address will not be published. Required fields are marked *