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
Paula
Helper I
Helper I

Rank with the same numbers

Hello! i'm having some problems whit the function RANKX.

 

What can i do if i have the same amount in different categories and i want to do a rank? i want to see only the top 5, but the rank 5 is 11 times, so how can i choose some of them? 

 

Example:

Captura.JPG

 

 

Thanx!

9 REPLIES 9
v-jiascu-msft
Employee
Employee

Hi @Paula,

 

Which some of them do you want? Are there any other conditions? If it's possible to sort Amount in the Query Editor and add an index?

 

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-jiascu-msft

 

I have no preference. If I add an index, can I choose one of the categories and obtain the top 5? In case of adding an index, what would the DAX be like?

 

There is no other condition.

Greg_Deckler
Super User
Super User

Try specifying Dense as an option, the default is Skip.

 

https://msdn.microsoft.com/en-us/query-bi/dax/rankx-function-dax

 


@ 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...

Hi @Greg_Deckler

 

i'm using Dense, but the rank is the same when i have the same amount for different categories.

 

 

OK, so my understanding is that you want the top 5 categories. But, some of the categories have the same amount that you are ranking on. I guess my question is, how do you want it to work? In your scenario where you have a bunch of ranking "5", do you want it to randomly pick one? What is your expectation?


@ 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...

Hi @Greg_Deckler

 

Yes!, i want to choose one at random. Is that possible?

Possibly, you would want to use the RAND function to generate a random number. Alternatively, you could just use MAX or MIN on the category to grab the first or last one.


@ 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...

@Greg_Deckler

 

In which part of the function should i use RAND?

 

The function that i'm using is this:

 

Rank = (RANKX(FILTER('Tabla1'; 'Tabla1'[Id Periodo] = EARLIER('Tabla1'[Id Periodo]))); RANKX(ALL('Tabla1'); 'Tabla1'[Monto]; ;DESC;Dense)))

Well, that's going to be kind of interesting. You would need to create a second column/measure probably that looks at your rankings and identifies duplicates. You would use something like COUNTROWS for all rankings equal to EARLIER ranking. Then, if that is greater than 1, you would generate a random number using RAND between 1 and that number. Then you would assign that row the rank value and everything else would get +1 that rank. Going to get tricky though because if it is, say, rank 2 and you want the top 5 then all other ranks have to get pushed down. Might be better to go with SKIP in stead of DENSE in your RANKX in this case so that it isn't so much of a chore.

 

Can you post your data in text for so that it can be copied and pasted? I don't feel like typing all of that data.


@ 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.