Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Sylvine_Wyz
Helper IV
Helper IV

Top 10 and filter

Hi Everybody, 

 

I have a problem with the top 10 and the filter.

I created a top 10 of EAN's tyre by the quantity. It's correct. 

But when i add a filter in the visual to filter by agency, the top 10 became 11 or more.

 

Sylvine_Wyz_0-1596803136945.png

Sylvine_Wyz_1-1596803184247.png

Can you help me please ?

 

Thanks in advance. 

 

Have a nice day

 

1 ACCEPTED SOLUTION
Anand24
Super User
Super User

Hi @Sylvine_Wyz ,

 

You get more than 10 values with Top 10 filter because of common values and hence power bi by default dense ranks it.

You will need to create a rank column and pull this rank column in top 10 filter.

 

1. Create a new column with below dax; This column will set bifercation between same values :

Number+Random = 'Table'[number]+RAND()
 
2. Create another column for ranking and this will be pulled in top 10 filter:
RANX =
RANKX(
'Table',
'Table'[Number+Random]
,
,1
)
 
Note:
Names in my above example,
> Table Name = Table
> Field to filter top 10 = Number
> Column 1 created = Number+Random
> Column 2 created = RANX
 
Reference Image:
rank.PNG
 
Give a thumbs up if this post helped you in any way and mark this post as solution if it solved your query !!!
 

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

I am still very confused on this DAX for Top N. I simply could not achieve it to display Top 10 rows. See printscreen.

tanthiamhuat_0-1640697745313.png

 

tanthiamhuat_1-1640697824582.png

It always give more than 10 rows here, why is this so?

Anand24
Super User
Super User

Hi @Sylvine_Wyz ,

 

You get more than 10 values with Top 10 filter because of common values and hence power bi by default dense ranks it.

You will need to create a rank column and pull this rank column in top 10 filter.

 

1. Create a new column with below dax; This column will set bifercation between same values :

Number+Random = 'Table'[number]+RAND()
 
2. Create another column for ranking and this will be pulled in top 10 filter:
RANX =
RANKX(
'Table',
'Table'[Number+Random]
,
,1
)
 
Note:
Names in my above example,
> Table Name = Table
> Field to filter top 10 = Number
> Column 1 created = Number+Random
> Column 2 created = RANX
 
Reference Image:
rank.PNG
 
Give a thumbs up if this post helped you in any way and mark this post as solution if it solved your query !!!
 

Hello, 

 

Thanks everybody for your help. 

I found a solution in your post and with youtube's help.

 

Have a nice day ! 

Greg_Deckler
Super User
Super User

@Sylvine_Wyz - You might find this useful: https://community.powerbi.com/t5/Quick-Measures-Gallery/To-Bleep-with-RANKX/m-p/1042520#M452

 

and

 

https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Mythical-DAX-Index/td-p/1093214



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.