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
vilnyts
Frequent Visitor

Adding RAND() number to measure and RANKX() it

Hi, everyone.

I have a problem when i am using RANKX() function.

I have a one column table that contains redundant category names (AAA, BBB, CCC.....FFF) and have a measure that counts rows through a table:

Counts =
COUNT(
'table'[Dim]
)
 
I am going to rank my rows, to do that i have a measure:
RankRows =
RANKX(ALL('table'[Dim]), [Counts] ,, DESC)
 
When i put those measures (rank and count) and column into a table i have the following:
vilnyts_0-1657692232304.png

As you can see categories EEE, FFF have the same counts and as a result the same rank.

I want to get rid of this, i need to get the unique rank for the each row.

To do that, "Counts" measure was modified a bit i added random number and expected to get correct rank, but i have this:

vilnyts_1-1657692790581.png

"Counts" was modified like this:

Counts =
COUNT(
'table'[Dim]
) + DIVIDE(RAND(), 100)
 
Could somebody help or explain way, how to add to measure random number and after RANKX() through this value.
I am expecting from above example something like this:
DDD, 8, 1
CCC, 7, 2
BBB, 6, 3
AAA, 5, 4
EEE, 4, 5
FFF, 4, 6
 
Thank you in advance.
Have a good day)
1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi,

Please check the below measures and the attached pbix file.

I suggest writing an additional measure like below.

 

Rankalphabet = 
RANKX ( ALL ( 'Table'[Dim] ), CALCULATE ( MAX ( 'Table'[Dim] ) ),, ASC ) / 100

 

Counts = 
COUNT(
'Table'[Dim]
) + [Rankalphabet]

 

RankRows = 
RANKX(ALL('Table'[Dim]),  [Counts],, DESC)

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

2 REPLIES 2
vilnyts
Frequent Visitor

Hi,

Thanks a lot Jihwan_Kim, it was the solution for me.

Have a good day.

 

Jihwan_Kim
Super User
Super User

Hi,

Please check the below measures and the attached pbix file.

I suggest writing an additional measure like below.

 

Rankalphabet = 
RANKX ( ALL ( 'Table'[Dim] ), CALCULATE ( MAX ( 'Table'[Dim] ) ),, ASC ) / 100

 

Counts = 
COUNT(
'Table'[Dim]
) + [Rankalphabet]

 

RankRows = 
RANKX(ALL('Table'[Dim]),  [Counts],, DESC)

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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.

Top Solution Authors