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

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

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

8 thoughts on “Power Query Parameters Vs. What If Parameters: Power BI Implementation Use Cases

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

Leave a Reply

%d bloggers like this: