What if Parameter in Power BI filter

In this Power BI article, we will discuss how to use the what if parameter Power BI filter.

Filter Data Dynamically using the What if Parameter in Power BI

Let us see how we can filter a Power Bi report data dynamically using a What If Parameter filter in Power Bi.

In this example, we are going to use the sales table data, to filter the Top N customers based on the sales value using what-if parameter slicer visuals in Power Bi.

  • Open the Power Bi desktop, and load the data into it using the get data option. Once the data has been loaded, select the table visually and select the Customer name and total Sales fields from the field pane.
Filter Data Dynamically using the What if Parameter in Power BI
Filter Data Dynamically using the What if Parameter in Power BI
  • Create a new measure and apply the below-mentioned formula to find the top N customers using the Power BI Rankx function.
Rank = RANKX(ALL(Sales_Table[Customer Name]),Sales_Table[totalsales],,DESC)

Where,

  1. Rank = New measure name
  2. RANKX = Function Name
  3. Sales_Table = Table Name
  4. Customer Name = Column Name
  5. Total Sales = Existing measure name
  • Now in the Table visual drag and drop the created measure value to display the rank of the customers.
  • The screenshot below displays and ranks the customer names based on the sales value.
What if Parameter Power BI filter
What if the Parameter Power BI filter
  • Create a new measure and apply the below Power Bi Dax formula to find the Top N customer’s value.
Top N customers = IF([Rank]<=10,Sales_Table[totalsales],BLANK()) 

Where,

  1. Top N customers = New measure name
  2. Rank = existing measure name
  3. Sales_Table = Table Name
  • Now in the Table visual drag and drop the created measure value to display the sales value for the top 10 customers bypassing the static value and for the remaining rank values it displays the blank value.
  • In the below screenshot, you can see that the measured value displays the value based on the condition applied.
What if Parameter Power BI filter example
What if the Parameter Power BI filter example
  • Now we can change the table visual to the clustered column chart visual, and remove the rank and total sales fields from the field values.
  • And you can see that the below-clustered column visually displays the Top N customers by their Customer Name.
Example of What if Parameter Power BI filter
Example of What if Parameter Power BI filter
  • To filter the customer name dynamically we have to create a what-if parameter. To achieve it follow the below steps:
  • Now Click the Modeling tab in the top ribbon and select the What IF parameter as highlighted below:
Add What if Parameter in the Power BI
Add What if Parameter in the Power BI
  • A What if parameter pop window appears, In the Name section we can enter the parameter name as TOP N CUSTOMERS.
  • In the Data type, we can select the data type as a whole number. We can also set Minimum value, Maximum value, Increment, and Default value.
  • To save the What-if parameter click on the ok button below:
Filter Data Dynamically using the What if Parameter in Power BI example
Filter Data Dynamically using the What if Parameter in the Power BI example
  • Under the Fields pane, we can see that the Parameter has been created as a New Table
  • Once the parameter has been created. It automatically creates two measure values for the generated series and for the selected value.
See also  What if Parameter in Power BI


For generated series:

TOP N CUSTOMERS = GENERATESERIES(1, 10, 1)

For Selected Value:

TOP N CUSTOMERS Value = SELECTEDVALUE('TOP N CUSTOMERS'[TOP N CUSTOMERS], 1)
Filter Data Dynamically using What if Parameter in Power BI example
Filter Data Dynamically using the What if Parameter in Power BI example
  • Now open the existing Top N customers measure value and replace the static value 10 as TOP N CUSTOMERS Value from the TOP N CUSTOMERS table.
Top N customers = IF([Rank]<='TOP N CUSTOMERS'[TOP N CUSTOMERS Value],Sales_Table[totalsales],BLANK()) 

Where,

  1. Top N customers, Rank = Existing measure value
  2. Sales_Table, TOP N CUSTOMERS = Table Names
  • In the below screenshot, you can see that it displays the default value as 1 and in the clustered column chart it displays the customer name.
Filter Data using What if Parameter in Power BI example
Filter Data using What if Parameter in Power BI example
  • In the same way, we can slide the parameter value to filter and display the data based on the selection.
  • Here if I choose the parameter value as 4 in the chart visual it displays the top 4 customer names.
Filter Data using What if Parameter in Power BI
Filter Data using What if Parameter in Power BI

This is how to filter the Power Bi report data dynamically using the What If Parameter filter in Power Bi.

In this Power BI article, we have discussed how to use the What if Parameter Power BI filter in a Power BI report.

You may like the following Power BI tutorials:

>