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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Yiyi
Helper I
Helper I

How to create a rank column with only distinct numbers? +RAND() is not working.

Hello kind friends :), 

 

Could you please have a look at my DAX code below? I am creating a table which calculates the distinct count for each media sources and I also trying to add a column of ranks for the purpose of calculating running total later. I need  distinct numbers for my ranks otherwise the running total won't work. In order to achieve this goal, I used +rand() to avoid ranks with same values, but the result looks pretty weird. Do you know why? Thanks a lot! 

 

Table = VAR _t= SUMMARIZE(
            ALLSELECTED('Main Data'),
            'Main Data'[Media],
            "Unique_Item_Count", DISTINCTCOUNT('Main Data'[HeadlineID])
        )
RETURN 
ADDCOLUMNS(
    _t,
    "Rank",
    RANKX(_t,[Unique_Item_Count]+RAND(),,DESC,Dense)  
)

 Result: 

Yiyi_0-1690916752704.png

 

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

Hello @Yiyi 

Using RANX with RAND() in this way is not reliable.

 

RANKX works by determining the position of the current value "within" the list of values computed for each row of the table.

Using your existing expression, RAND() will be added to both the "current" value and the values per row of the table independently, which will inevitably result in ranks that occur between the values corresponding to rows of the table.

 

I would instead suggest using RANK, which can use the Media column to break ties.

Change the RANKX expression to:

RANK (
    DENSE,
    _t,
    ORDERBY ( [Unique_Item_Count], DESC, 'Main Data'[Media], ASC ),
    DEFAULT
)

Does this work for you?

 

Regards


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

2 REPLIES 2
OwenAuger
Super User
Super User

Hello @Yiyi 

Using RANX with RAND() in this way is not reliable.

 

RANKX works by determining the position of the current value "within" the list of values computed for each row of the table.

Using your existing expression, RAND() will be added to both the "current" value and the values per row of the table independently, which will inevitably result in ranks that occur between the values corresponding to rows of the table.

 

I would instead suggest using RANK, which can use the Media column to break ties.

Change the RANKX expression to:

RANK (
    DENSE,
    _t,
    ORDERBY ( [Unique_Item_Count], DESC, 'Main Data'[Media], ASC ),
    DEFAULT
)

Does this work for you?

 

Regards


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

It works perfectly. Thanks so much for your time 😊

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors