Forum Discussion
Power BI: Dynamic Top N Filtering Based on Selection?
I have a Power BI table with three columns: ID, Title, and Name.
I created two clustered bar charts:
- Chart 1 → Shows the Top 3 Names (Y-axis: Name, X-axis: Count of ID).
- Chart 2 → Shows the Top 3 Titles (Y-axis: Title, X-axis: Count of ID).
Each chart has a visual-level filter applied to show only the top 3 based on ID count.
Issue:
When I select a Name in Chart 1, Chart 2 updates, but it does not recalculate the top 3 Titles based on the selected Name—it just filters the existing top 3 Titles. Similarly, selecting a Title in Chart 2 does not dynamically update the top 3 Names in Chart 1.
Desired Outcome:
If I select a Name, I want Chart 2 to show the Top 3 Titles related to that Name (not just filter the default top 3). Likewise, selecting a Title should update Chart 1 to show the Top 3 Names related to that Title.
Note: There are more than 1000 records in my table, and there are more than 20 distinct Titles and Names each.
Is there a way to achieve this in Power BI?
Hi adi38612 ,
According to your question, you want to filter the first table (e.g. name) and then sort based on the values filtered by the first table right?
We can first use the RANKX function on the name column and total column sorting, and then create a new Topn table to Topn table to filter you need the first few rankings, and finally write a measure to put into each chart to achieve your needs!Name ID Count = CALCULATE(COUNT('Table'[ID]), ALLEXCEPT('Table', 'Table'[Name]))Rankx Name = RANKX(ALLSELECTED('Table'[Name]),[Name ID Count],,DESC,Dense)Top N = GENERATESERIES(1, 10, 1)Top N Names = IF([Rankx Name] <=SELECTEDVALUE('Top N'[Top N]), 1, 0)If you have any other questions, you can check out the pbix file I uploaded, I hope it helps, and I'd be honored if I could solve your problem!
Hope it helps!
Best regards,
Community Support Team_ Tom ShenIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi Adi38612,
Does your ID column have unique values? How is the ID column made up? Is it related to the Name column?
Thanks!
Maral
Hi adi38612 ,
According to your question, you want to filter the first table (e.g. name) and then sort based on the values filtered by the first table right?
We can first use the RANKX function on the name column and total column sorting, and then create a new Topn table to Topn table to filter you need the first few rankings, and finally write a measure to put into each chart to achieve your needs!Name ID Count = CALCULATE(COUNT('Table'[ID]), ALLEXCEPT('Table', 'Table'[Name]))Rankx Name = RANKX(ALLSELECTED('Table'[Name]),[Name ID Count],,DESC,Dense)Top N = GENERATESERIES(1, 10, 1)Top N Names = IF([Rankx Name] <=SELECTEDVALUE('Top N'[Top N]), 1, 0)If you have any other questions, you can check out the pbix file I uploaded, I hope it helps, and I'd be honored if I could solve your problem!
Hope it helps!
Best regards,
Community Support Team_ Tom ShenIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Thanks Anonymous, It worked like a charm.
Quick update: the 'Filter' interactions between the two visuals should be enabled, both ways!
