There are two types of parameters in Power BI, Power Query parameters, and What if parameters. In this article, I’m going to explain a very useful use case of the what-if parameters, and if you haven’t ever worked with it, it is a great way to understand what is What If parameters and what things you can do with it. The What If parameters are also called informally as DAX parameters. If you like to learn more about Power BI, read Power BI book from Rookie to Rock Star.
Video
What If Parameters
There are two types of parameters in Power BI; Power Query parameters, and What If parameters. Power Query parameters are used for creating a dynamic structure for data transformation phase. Here is an example of using Power Query parameters for changing the data source, and another example of creating a custom function to loop through a number of steps for a single data structure.
The What If parameters, on the other hand-side, are for users to make changes and see the effect of their changes immediately on the report. For example, let’s say you have written a DAX expression that calculates sales of last month. After building the solution using this calculation and delivering it to your users, they come to you and ask you that can we have this calculation for two months ago? they come after a while and ask can we have it for three months ago or even 6 months?
The user is seeking for a way that they can change a calculation by their selection in the slicer. They want to see what would happen if they change some of the values. They want to do What IF analysis. That is exactly why this type of parameter is called What If parameters. Let’s see that through an example.
If the user of Power BI report wants to do analysis such as WHAT would happen IF this value is different, then you can implement that using the What If Parameter.
Sales Last Month
The DAX measure below calculates the sales of last month. If you want to know more about how this calculation works, read my article here.
Sales Last Month =
CALCULATE(
SUM(FactInternetSales[SalesAmount]),
PARALLELPERIOD(
DimDate[FullDateAlternateKey].[Date],
-1,
MONTH
)
)
Using the calculation above, you can see that we navigate one month back using the -1 as the second parameter of the ParallelPeriod function to calculate the sales of the last month.
here is the result of that calculation:
For every month, the value of this measure would be the sales of the month before that.
What If X Months?
After delivering a solution with the above calculation to your users, you will get very likely another request: what if I want to see the sales of 2 months ago? 3 months ago, 5, months etc. What if I want to see the sales of X months ago. You don’t expect end-users to go and change the “-1” in the DAX expression to another number and get the result (even if they do have the edit access to the report, or they do have Power BI Desktop installed). You need to give them the ability to change the “-1” in the expression above with merely changing a value in a slicer.
Creating What If Parameter
You can easily create a What If parameter in Power BI Modeling tab.
For this purpose, the data type of the parameter can be Whole number, I named it as “How Many Months Back”, and set the values as below. Having the “Add slicer to this page” ensures that there will be a slicer created for this parameter in the current report page.
After this step, you’ll see a new slicer created and new table, column and measure with this name.
When you create a parameter, Power BI uses the GenerateSeries DAX function to create the list of values as a calculated table. To learn more about this function, read my article here.
There is also a measure created with this new table, that uses SelectedValue function to access the value selected by the slicer. You can add that measure to a card visual and see how it changes when you change the slicer value.
Using the Parameter Selected value in the measure
Now that we have created the parameters, we can move to the next step, which is using it to change the value of the measure to calculate sales of X months ago;
Sales X Months ago =
CALCULATE(
SUM(FactInternetSales[SalesAmount]),
PARALLELPERIOD(
DimDate[FullDateAlternateKey].[Date],
-1*[How Many Months Back Value],
MONTH
)
)
Note that in the above expression, we have used the selected value measure multiplied by -1, the reason is that our parameter is a positive value, but for moving back a few months ago, we need a negative one. You can create parameters with negative values too, However, it might not look great from the user point of view, because they have to understand why the slicer value is negative.
Testing the result
Now, simply with a change in the slicer, the calculation changes. You gave the user the ability to calculate the sales of one month ago, two months, three months etc, using a What If parameter.
Summary
The What If parameters in Power BI are very useful for creating a dynamic DAX measure expression that changes based on the user selection of a slicer. This can help the user to understand what would happen if they change a value. You can also create parameters of other data types (DateTime, and Text), but the graphical user interface doesn’t support those at the time of writing this article, for those you need to create your own parameter table which I’ll explain about that later in another article.
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.