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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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