Dynamic Top N Filtering in Power BI Using DAX

Overview

Dynamic top N filtering in Power BI refers to a technique that allows users to interactively control how many top-performing items (like products, regions, or categories) are displayed in a visual - based on a metric such as sales, profit, or quantity - using DAX formulas and slicers.

Instead of hardcoding the number of top items (e.g., Top 5 or Top 10), you create a parameter or slicer that lets users choose the value of N themselves. Making the dashboards more flexible and user driven.

Step-by-Step Guide

In this example, using the Orders table of the Sample – Superstore dataset, lets create a view that shows the top 7 product Sub-Categories by Sales.

To do so, lets first create a measure “Total Sales” as shown below.

Total Sales = SUM(Orders[Sales])

Next, create another measure “Top N” to compute the top 7 product Sub-Categories by sales using the formula below.

Top N = CALCULATE([Total Sales], KEEPFILTERS(TOPN(7, ALLSELECTED(Orders[Sub-Category]),[Total Sales], DESC)))

Building the View

Next, create a view (a bar plot).

• Add Sub-Category to Y-axis.

• Add Top N to X-axis.

• Label the viz.

See the resulting view

(Notice the resulting view shows the top 7 product Sub-Categories by Sales – as per our specifications in the calculation “Top N”)

Let’s make the viz dynamic by including a parameter to enable users to select the N value based on their analysis goals.

Creating a Parameter

Create a parameter by going to the modeling tab >> and select New parameter…

Configure your parameter as shown below.

(In this case, I have created a parameter “Select Top N”, where I have set the minimum selectable value as 1 and the maximum selectable value as 15, with the default value being 7 and an increment of 1)

Next, lets link our parameter and our view by replacing the value 7 on the calculation “Top N” with the parameter “Select Top N” as shown below.

Notice that when you change the parameter value the viz dynamically changes to show the top N Sub-Categories as per your selection on the parameter.

See the gif below.

Conclusion

Dynamic top N filtering in Power BI using DAX is more than just a technical trick - it’s a strategic enhancement that transforms static dashboards into interactive, user-driven experiences. By leveraging DAX functions like TOPN, and FILTER, alongside slicers or parameters, report creators empower users to explore data on their own terms. Whether it's surfacing the top-performing products, regions, or sales reps, this approach offers clarity and focus in a sea of information.

The real beauty lies in its flexibility. Instead of building multiple visuals for different thresholds, a single dynamic setup adapts instantly to user input, reducing clutter and improving performance. It also encourages deeper engagement, as users can tailor insights to their specific needs - whether they’re executives scanning for top-line trends or analysts digging into granular performance.

In a world where data storytelling is key, dynamic top N Filtering is a powerful tool in the Power BI arsenal. It bridges the gap between raw data and actionable insight, making dashboards not only smarter but also more intuitive. As you continue to refine your reports, consider how dynamic filtering can elevate the user experience and bring your data narratives to life.

If you like the work we do and would like to work with us, drop us an email on our contacts page and we’ll reach out!

Thank you for reading!!