Ranking a measure

[Anonymous]

In the below table, I have 2 columns "Name" and "Amounts" and 3 measures. However the measure "Rank of Random Values" doesn't return expected results

1.     Rank of Amount =RANKX(   ALLSELECTED(Table1),      CALCULATE(SUM(Table1[Amounts]))      ,,ASC)    //( It ranks the values in 'Amounts' column.)

2.     Random values = RAND()*[Rank of Amount]        //(It multiplies the rank value with a random number)

>>    Rank of Random values = RANKX(

                                        ALLSELECTED(Table1),
                                        CALCULATE([Random values ])            ,,ASC)

 
Here I'm trying to rank the 'Random values' measure, but getting wrong values for Rank of Random values measure. Could someone please explain what I'm doing wrong here?

[marcorusso · Accepted solution]

You should store the random value in a calculated column, otherwise, it is recalculated at every execution or - worse - it is reused from previous calculation without recomputing it.

[marcorusso · Accepted solution]

You can in DAX, but you do not have the necessary control over the complete DAX query when you use Power BI. All the dynamic calculations (measures) are applied to the same visual at once, and this makes it very complex if not impossible to create a ranking with values you must consolidate in a previous step.

[marcorusso · Accepted solution]

You should store the random value in a calculated column, otherwise, it is recalculated at every execution or - worse - it is reused from previous calculation without recomputing it.

[Anonymous]

@marcorusso   Thank you so much for the reply. What I wanted to do with the measure "Rank of Random" was to use it to display random rows of the dataset, like a random sample of data.
I have a user input field that takes the number of rows user wants to be displayed.
ie, if user wants 10 rows, I need to display 10 rows and for that I intend to use the measure value. It should not be based on any ranking, which is why I used the rand() function.
Initially, rank all the rows based on any value (Rank of Amount ), then multiply that rank with rand()   to get a random value (Random values)  and then rank this measure (Rank of Random values)  to be used in displaying the rows in table.
Do you think there is any other way of doing that?

[v-angzheng-msft]

Hi, @Anonymous 

That's a really good question.
No doubt you are on the right track, I just tried that too, but the random values seem to be recalculated in the RANK function or something else, and I also tried to rank by summarizing a virtual table but that didn't work either. It doesn't seem like a difficult problem, but it does make me wonder too, and it will take me some more time to study how the DAX formula works.

Best Regards,
Community Support Team _ Zeon Zheng

[Anonymous]

Thank you for the response, please let me know in case you find a solution.