Creating an interactive Dynamic TOPN Selection in Power BI for Real-Time and Dynamic Insights

[ajaybabuinturi]

Everyone knows how to get Top N metrics using Top N Filter type in the Filters section/pane or by using TOPN DAX function. However, some businesses require a more dynamic and interactive Top N selection to meet their analytical needs. In this blog, I will walk you through the steps to achieve a dynamic/interactive Top N selection in Power BI.

Step1: Create a Numeric Parameter for Top N Selection

After importing sample data into Power BI, we need to create a numeric parameter for Top N selection.

• Navigate to the Modelling (1) tab → Click on Parameters (2) drop down → Select Numeric range (3).
  
• Rename the parameter (4) and set the minimum, maximum and increment (5) values based on business needs. (Here I used Name: Top_Parameter, Min: 0, Max: 20 and Increment: 5 for presentation purpose). Then hit the Create button.
• The increment value ensures that to create a parameter range from minimum to maximum values.
  
• The TOP_Parameter output will look like a numeric range for slicer selection as below.
  

  
  Step 2: Replace Hardcoded TOPN Number with Dynamic Parameter

• Once the parameter is created, replace the hardcoded TOPN number (6) in the TOP 5 measure with the newly created TOP_Parameter. You can achieve this using one of two methods either 1 or 2.
  
• Updated measure as below
  
• After the above point, create a visual using the modified TOPN measure. The results will dynamically change based on the selected Top N value.   
  
  
  

  ***Addressing Business Needs with an Adaptive Model Count***

• So far, we have made the Top N selection dynamic, but have we truly addressed all business needs?

  NO, because we don’t yet know how many ‘Models/Categories’ exist in the dataset. To account for this:

• Modify the TOP_Parameter to dynamically count all Models/Categories. For that you need to change the measure as below to find out the maximum number of the Models/Categories. The list will extend to the maximum value, ensuring that any newly added ‘Model’ in the future is automatically included. 
  
• Create a new measure (IsTop) in the TOP_Parameter table to find whether the selected Model/Category exist in the TOPN list. Which will help us to get the selected TOPN Models for visualization.
  
• After completion of IsTop measure, add it to the visual Filters Section/Pane and set the filter to show the items where the value is 1. This will dynamically display results based on the selected Top N value.
  

By following this approach, we have successfully implemented an interactive and dynamic Top N selection in Power BI. This method allows businesses to analyse the top-performing categories (Ex: Sales, Models, Products) based on a user-defined Top N value. Additionally, we have addressed the challenge of tracking all available models dynamically, making the solution future-proof for evolving business requirements.

Now, with this setup, you can easily explore insights based on your selection and drive more meaningful data-driven decisions in Power BI.

Attachments: Sample BI report and data.

TOP_N Dynamic -.pbix

[suparnababu8]

Good content. Thanks for sharing!!

[Magudeswaran_MR]

Useful information and thanks for sharing the blog