TOP N visual level filter & page slicer not working

[Clikojo]

Hi,

I have applied a TOP N visual level filter to a graph that shows me top 10 postcodes when no slicer selection is applied. The visual is fine.

When I select products from my slicer, for some selections it will show top 10 but for most it does not stick to top 10 and will show more. I only have 1 table imported into my report so I find this weird. 

Thanks in advance!

[ArwaAdoudOmer]

The issue occurs because the TOP N filter applies before the slicer selection. When you select a product, the ranking recalculates, and if multiple postcodes have the same sales amount (e.g., all top ones have $200 in sales), more than 10 results may appear.

Use the RANKX function in DAX to dynamically rank postcodes based on slicer selections:

Create a measure that calculates the rank of each postcode based on sales.
Apply a visual-level filter to show only the top 10 postcodes based on the rank.

For more details, check Microsoft Learn:
https://learn.microsoft.com/en-us/dax/rankx-function-dax 

If this response was helpful, please accept it as a solution or give kudos to help other community members

[DataNinja777 · Accepted solution]

Hi @Clikojo ,

The issue with the TOP N visual-level filter showing more than 10 postcodes when applying a slicer selection is likely due to how Power BI evaluates ranking dynamically with filter context changes. If multiple postcodes have the same value at the 10th position, Power BI will include all of them, leading to more than 10 entries. Additionally, when selecting a product, the measure used in TOP N might return fewer or more than 10 postcodes because some products may not have data for exactly 10 unique postcodes. Another possible reason is that the filter granularity may not align properly with the applied selection.

A better approach is to use a RANKX measure instead of relying on the visual-level TOP N filter. You can create a measure to rank postcodes dynamically, ensuring that only the top 10 appear based on the selection:

Rank_Postcode = 
RANKX(
    ALLSELECTED('YourTable'[Postcode]), 
    CALCULATE(SUM('YourTable'[Sales])), 
    , DESC, DENSE
)

After defining the ranking, apply a filter measure to restrict the visual to only the top 10 postcodes:

Show_Top10 = 
IF([Rank_Postcode] <= 10, 1, 0)

This measure should be used as a visual filter, setting it to display only when the value equals 1. If the issue persists due to tied rankings causing more than 10 postcodes to appear, modifying the RANKX function to use the SKIP parameter instead of DENSE can help ensure that only 10 unique postcodes are included:

Rank_Postcode = 
RANKX(
    ALLSELECTED('YourTable'[Postcode]), 
    CALCULATE(SUM('YourTable'[Sales])), 
    , DESC, SKIP
)

Additionally, checking the interaction settings between the slicer and the visual can ensure that the slicer is filtering rather than highlighting, which could affect the ranking calculation. If needed, this can be adjusted by selecting the slicer, going to "Format" > "Edit Interactions," and ensuring that the graph is set to be filtered properly. This method should help maintain a strict top 10 list regardless of slicer selection.

Best regards,

[FarhanJeelani]

Hi @Clikojo , The issue you're facing is likely due to the way TOP N filtering interacts with slicers in Power BI. Here’s why it happens and how to fix it:

Why is the TOP N Filter Not Working?
TOP N is applied after slicers: When you apply a slicer (e.g., selecting products), the dataset is filtered first, and then the TOP N logic is re-evaluated.
Some selections have less than 10 values: If fewer than 10 postcodes exist for the selected products, it may show fewer results.
Ties in values: If there are multiple postcodes with the same value at the 10th position, Power BI will include all of them, causing the visual to exceed 10 rows.

Solution 1: Create a Measure for Dynamic TOP N Filtering
Instead of using a visual-level filter, create a rank measure and use it in a visual filter.

Step 1: Create a Ranking Measure

PostcodeRank =
VAR SelectedMeasure = SUM('Table'[Sales]) -- Change this to your relevant measure
RETURN
RANKX(ALLSELECTED('Table'[Postcode]), SelectedMeasure, , DESC, DENSE)

Step 2: Apply a Visual-Level Filter
Add the PostcodeRank measure to your visual.
Set the filter: PostcodeRank is less than or equal to 10.

Please mark this post as solution if it helps you. Appreciate Kudos.