cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Paula Regular Visitor
Regular Visitor

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
Super User
Super User

Re: Rank with the same numbers

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

 

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

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Community Support Team
Community Support Team

Re: Rank with the same numbers

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.
Paula Regular Visitor
Regular Visitor

Re: Rank with the same numbers

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.

Paula Regular Visitor
Regular Visitor

Re: Rank with the same numbers

Hi @Greg_Deckler

 

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

 

 

Super User
Super User

Re: Rank with the same numbers

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?


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Paula Regular Visitor
Regular Visitor

Re: Rank with the same numbers

Hi @Greg_Deckler

 

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

Super User
Super User

Re: Rank with the same numbers

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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Paula Regular Visitor
Regular Visitor

Re: Rank with the same numbers

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

Super User
Super User

Re: Rank with the same numbers

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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!