Forum Discussion
Solved
Rank Multiple Measures
Hi
I've searched the forums but can't find an answer to my particular question.
I'm trying to rank one column against the values in other columns as shown below. I want the rank of the column Test_1 based on columns Test_1 to Test_5 where Test_1 to Test_5 are measures. Does anyone know if this is possible?
Thanks
| Year | Month | Test_1 | Test_2 | Test_3 | Test_4 | Test_5 | Rank_Test_1 |
| 2016 | March | 6.098371 | 6.44627 | 6.837873 | 5.820597 | 6.407869 | 4 |
| 2016 | April | 6.200762 | 6.5536 | 6.873662 | 6.003421 | 6.471136 | 4 |
| 2016 | May | 6.431377 | 6.465101 | 6.847985 | 5.908099 | 6.082241 | 3 |
| 2016 | June | 6.034497 | 6.587486 | 6.919705 | 6.221608 | 6.479582 | 5 |
One possibility:
aTest = var vTest_1 = [Test_1] return 1 + IF([vTest_1]<[Test_2],1,0) + IF([vTest_1]<[Test_3],1,0) + IF([vTest_1]<[Test_4],1,0) + IF([vTest_1]<[Test_5],1,0)Cheers!
Nathan
Another way could be
Measure = VAR temp = { [Test_1], [Test_2], [Test_3], [Test_4], [Test_5] } RETURN COUNTROWS ( FILTER ( temp, [Test_1] <= [Value] ) )
5 Replies
One possibility:
aTest = var vTest_1 = [Test_1] return 1 + IF([vTest_1]<[Test_2],1,0) + IF([vTest_1]<[Test_3],1,0) + IF([vTest_1]<[Test_4],1,0) + IF([vTest_1]<[Test_5],1,0)Cheers!
Nathan
Thanks Nathan ! That works perfectly.
Another way could be
Measure = VAR temp = { [Test_1], [Test_2], [Test_3], [Test_4], [Test_5] } RETURN COUNTROWS ( FILTER ( temp, [Test_1] <= [Value] ) )Thanks Zubair! That also works.
