Home » What If Parameter Power BI

What If Parameter Power BI

What if Parameter gives you the ability to transform data dynamically. Using this you can easily evaluate you data behaviour under different conditions dynamically.





Lets say for below given Sales data table, you want to see how your sale’s amount is affected after applying various discount.

You can simply create a What if parameter to pass the discount value dynamically to the Amount Measure that you are using to calculate Sales Amount, and can see the affected amount after applying certain discount.

Lets click on Modeling Tab in Ribbon Bar, then Select New Parameter as shown below.

After clicking on New Parameter, a What-If parameter dialog box open.

Now you need to provide a details for Parameter as following:

Name: provide the name for parameter.

Datatype: data type for values in parameter.

Minimun, and Maximum value: specify the minimum and maximum value for parameter.

Increment: this is how much the parameter will adjust when interacted with in a report.

Default: is a default value for parameter.

Add Slicer to this Page: when this is checked, It puts a slicer with your what-if parameter onto the current report page automatically.

After providing details click on Ok Button.

Once you click on Ok button, a slicer is created on report page with values (minimum to maximum) that you assigned to parameter.

Values will be incremented by 1 upto the maximum value on scrolling slider to right side.

Also a What if parameter is created in data model. It contains a Parameter, and Measure. Both the parameter and the measure become the part of data model.

Parameter is that returns a single column table containing the values ( minimum 1 to maximum value 20).

It is used to show the values into visual, the slicer on report page is using the Parameter to displaying the values from 0 to 20, and a measure named Parameter value, returns the value selected in parameter.

Lets give the meaningful name to paramter, change the name to Discount as shown below.

 

Lets take the Sales Data into table Visual as shown below.

Using What-If Parameter in Measure

Lets create a measure named Amount After Discount that will returns the affected sale’s amount after giving discount, and the discount value will be passed dynamically through slicer using What If parameter.

Amount After Discount =
SUM ( Sales[Amount] )
– ( SUM ( Sales[Amount] ) * Parameter[Parameter Value] ) / 100

 

Once you commit the DAX, Lets drag it into table visual right to the Amount field.

And you can see, the Default value that set to parameter is 0 so the values in Amount After Discount fields are same as Amount that means 0% discount is applied on Sale’s Amount.

Lets scroll the slider slowly to the right side and set the parameter value to 10 to see the affect on Sale’s Amount after applying 10% discount.

You can see, After setting up the Parameter value on slicer to 10, the 10 % discount is applied on Sale’s Amount immediately.

So using What-If parameter you can easily evaluate you data behaviour under different condition by passing values dynamically to the DAX calculation.

Modifying the What-If Parameter

You have created a What If parameter for maximun value 20 so you can not pass discount value more than 20, In case if you want to update the maximum value, minimun, increament value than you can also Edit the Parameter.

Following are the Steps to modifying the What-If parameter.

Go to Data view, then Under Fields Pane, Click on Parameter.
You can see the DAX code for Parameter, It is a GENERATESERIES function Dax.

You can modify it and provide minimum, maximum and increate values.

In the first arugment of GENERATESERIES Function, provide a starting value that will be a minimum value that is 1, in second argument provide ending value that will be your maximum value that is 50 and in third arugment provide the increment value that is 2.

 

After modifying the DAX code, commit the dax and go to report page.

You will see now values in slicer has been changed starting from 1 and that is incremented by 2 up to the maximum value.

Now you can see the affected amount on more than 20 % discount. Lets see the affected sale’s amount

Also Read..

Implementing Parameters in Power BI

Changing the Data Source dynamically using Parameters



SQL Basics TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More




Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.