How to create a dynamic data filter in Power BI?

by

Here’s a question from a friend’s colleague:
“How can I create a dynamic top N filter in Power BI to show the top performing employees based on sales, where N can be changed by the user?”

Power bi dynamic filters based on selection


In this tutorial, we’ll create a dynamic top N filter in Power BI to showcase top-performing employees based on sales. The solution will consist of an interactive employee ranking dashboard. In this post, we’ll show how to allow users to interactively change the number of top performers displayed and filter data accordingly.

Data Preparation

  • In Power BI, create the EmployeeSales table containing the following fields: EmployeeID (Text), EmployeeName (Text), Department (Text), SalesAmount (Decimal) and SalesDate (Date)
  • Create a new table to keep the N values using the following DAX expression:
Top N Values = GENERATESERIES(1, 10, 1)

Setting up the Power BI selection filter

  1. In Power BI desktop, create a measure that will represent the total sales amount:
Total Sales = SUM(EmployeeSales[SalesAmount])
  1. Add a slicer for Top N selection:
  • Insert a new slicer visual.
  • Link the Top N Values {Value] to the slicer
  1. Create a measure to hold the selected value from the slicer.
Selected N = SELECTEDVALUE('Top N Values'[Value])
  1. Create a measure to identify top N employees:
Is Top N = 
VAR CurrentRank = 
    RANKX(
        ALL(EmployeeSales[EmployeeName]),
        [Total Sales],
        ,
        DESC
    )
RETURN
    IF(CurrentRank <= [Selected N], 1, 0)
  • The RANKX function is used to rank the company’s employees based on their total sales figures.
  • The SELECTEDVALUE function retrieves the user’s selection from the slicer.
  • The Is Top N measure uses a variable to store the rank and then compares it to the selected N value.

Filtering a Power BI table visual dynamically

  1. Create a visual to display the top sales performers:
  • Add a table or matrix visual to your Power BI report, place it at the right hands side of your slicer.
  • Add the EmployeeName and Total Sales fields to the table or visual
  • Apply the “Is Top N” measure as a filter, filtering for the value 1 – as shown below
  1. Create a dynamic title:
Dynamic Title = 
"Top " & [Selected N] & " Employees by Sales"

Dynamic selection dashboad

Now we are pretty much ready with our simple dashboard, filtered for the top 6 sales people 🙂

Troubleshooting

If the filter isn’t working correctly, ensure that:

  1. The Total Sales measure is calculating correctly.
  2. The slicer is properly connected to the Selected N measure.
  3. The Is Top N measure is applied as a filter to your visual.
  4. Make sure that there are no conflicting filters on your visual or page.
  5. If employees with the same sales amount are being cut off, consider using RANKX with DENSE ranking mode to handle ties appropriately.