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.

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

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

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

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

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

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

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)

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

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

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:

• How to sort slicer by measure in Power BI
• How to sort slicer by another column in Power BI
• Power BI combine columns from two tables
• Power BI Slicer Sort Descending
• Power BI Date slicer only shows dates with data
• What if parameter Power BI Date