RANKX tie breaker (DAX not M)

[smpa01]

Hello,

I am writing a DAX query and my dataset is following.

Table = DATATABLE (
        "Account#", INTEGER,
        "total", DOUBLE,
        
        {
            { 980012, 1200 },
            { 980012, 1200 },
            { 980012, 1200 },
            { 980012, 1300 },
            { 980013, 5000 },
            { 980013, 5000 },
            { 980013, 7800 }
        }
    )

Account#	total
980012	1200
980012	1200
980012	1200
980012	1300
980013	5000
980013	5000
980013	7800

I was wondering if there is a way to create a M like indexing through a calculated column (not meaure) in this table like following. I want to rank the total by Account#-total combined category.

Account#	total	Rank
980012	1200	1
980012	1200	2
980012	1200	3
980012	1300	1
980013	5000	1
980013	5000	2
980013	7800	1

I am trying to do this on a temp table through DAX query so I can't go back to M and create an index.

So far I tried this.

Is there a way to achieve the desired ranking by any chance in DAX query?

Thank you in advance.

[amitchandak]

Refer if this can help

https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures

[kentyler · Accepted solution]

Perhaps you could add another column, and in that column use the RAND() function to add a small decimal amount to each total. Then you rank on that column (which should have fewer chances of having a duplicate value), but display the real total column.

[smpa01]

@kentylerthanks it worked.

I do have a follow-up question for you though out of curiosity. Once RAND() values are obtained in step 1 is there a way to stop that from evaluating agin in subsequent steps/

[kentyler]

I don't understand your question. RAND has to be called for each row or the value it adds will not be different, and thus not able to provide a tie breaker.

[smpa01]

Let me clarify

the rand column value in VAR _3 is different than VAR_2.

Is there a way to keep the value of "rand" column obtained in VAR_2 unchanged in VAR_3?

VAR _2VAR _3