The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Power BI — Top N visual filter returning more than 10 rows due to ties (already tried rank (dense/skip created column for rank & summary table)
I have a Line and Stacked Column chart in Power BI where:
X-axis: Country
Column Y-axis: Measure = Sleeping Difference = Sleeping SFDC This Week – Sleeping SFDC Last Week
Line Y-axis: Another measure (not relevant to this issue)
I want to filter to Top 10 countries by absolute Sleeping Difference.
Sleeping Difference = [Sleeping SFDC This Week] - [Sleeping SFDC Last Week] ABS Sleeping Difference = ABS([Sleeping Difference])
Sleeping SFDC This Week
Sleeping SFDC This Week = CALCULATE( COUNT('Sleeping IB+Lost'[Equipment #]), 'Sleeping IB+Lost'[Week] = MAX('Sleeping IB+Lost'[Week]) )
Sleeping SFDC Last Week
Sleeping SFDC Last Week = VAR RankedWeeks = ADDCOLUMNS( VALUES('Sleeping IB+Lost'[Week]), "WeekRank", RANKX(VALUES('Sleeping IB+Lost'[Week]), 'Sleeping IB+Lost'[Week], , DESC) ) VAR SecondMaxWeek = MAXX(FILTER(RankedWeeks, [WeekRank] = 2), 'Sleeping IB+Lost'[Week]) RETURN CALCULATE( COUNT('Sleeping IB+Lost'[Equipment #]), 'Sleeping IB+Lost'[Week] = SecondMaxWeek )
When I use the Top N filter in the Visual Filter pane:
Top N = 10 by [ABS Sleeping Difference]
If multiple countries have the same value (ties), Power BI includes all tied countries, so I get more than 10 countries in the visual — sometimes 17, 20, or even 30.
Top N visual filter — Standard approach (Top N by ABS Sleeping Difference)
✅ Works for most cases, ❌ still returns >10 when there are ties.
Summary table with TOPN()
Top10Countries_SleepingDiff = TOPN( 10, SUMMARIZE( 'YourTable', 'YourTable'[Country], "AbsDiff", ABS([Sleeping Difference]) ), [AbsDiff], DESC )
Used TREATAS and visual filters to show only countries in this table.
❌ Still shows >10 due to ties.
RANKX approach
CountryRank = RANKX( ALL('YourTable'[Country]), ABS([Sleeping Difference]), , DESC, Dense )
Tried both Dense and Skip ranking methods.
Applied FILTER(CountryRank <= 10) in visuals.
❌ Still includes extra countries when ties occur at rank 10.
Manual visual filter using a rank column
Created a calculated column with rank.
Set visual filter to Rank <= 10.
❌ Did not solve the problem when multiple rows per country exist or when ties occur at the cut-off.
I’m looking for a robust approach to:
Show exactly 10 countries in the visual.
Break ties deterministically (for example, by adding a secondary sort like Country Name).
Avoid including all tied countries if they push the total above 10.
Country Week Sleeping SFDC This Week Sleeping SFDC Last Week
USA | 32 | 12 | 8 |
Canada | 32 | 20 | 16 |
Mexico | 32 | 5 | 8 |
UK | 32 | 9 | 2 |
Germany | 32 | 15 | 20 |
France | 32 | 11 | 15 |
Italy | 32 | 3 | 1 |
Spain | 32 | 25 | 21 |
Japan | 32 | 14 | 10 |
China | 32 | 18 | 19 |
India | 32 | 4 | 0 |
Brazil | 32 | 17 | 15 |
Australia | 32 | 8 | 8 |
Argentina | 32 | 6 | 3 |
South Africa | 32 | 7 | 1 |
Russia | 32 | 5 | 2 |
Netherlands | 32 | 9 | 4 |
If you know a DAX or modelling technique that will:
Always return exactly 10 countries,
Handle both positive and negative differences using ABS,
Break ties consistently without exceeding 10,
…please share your approach.
Hi @puneeth61
As we haven’t heard back from you, we wanted to kindly follow up to check if the suggestions provided by the community members for the issue worked. Please feel free to contact us if you have any further questions.
Thanks and regards
Hi @puneeth61
May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.
Thank you
Hi @puneeth61 ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.
Thank you.
Create a measure like
Rank =
RANK (
ALL ( Countries[Country] ),
ORDERBY ( [Abs Sleeping Difference], DESC, Countries[Country], ASC )
)
In the event of ties in the sleeping difference it will rank by country name in alphabetical order. Add this measure as the top n filter on the visual.
User | Count |
---|---|
10 | |
9 | |
6 | |
6 | |
5 |
User | Count |
---|---|
20 | |
15 | |
14 | |
10 | |
7 |