Forum Discussion

WesleyP8's avatar
WesleyP8
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago
Solved

Measure to return top ranked item

Hi all,

 

I'm trying to write a measure to return the highest ranked item, based on counts of rows. This seems like it should be straightforward but I'm struggling.

 

Here's the formula I'm using to produce the rank

 

Category Rank = RANKX(ALL(Data[Category]),[CountOfCategory])
 

Category

CountRank
Operations2342
Resources1453

Leadership

9811
   

 

I want to create a few measures that return me the category for each rank i.e.

 

Rank 1 category = "Leadership"

Rank 2 category = "Operations"

Rank 3 category = "Resources"

 

I've tried using this formula:

 

Rank 1 category = CALCULATE(SELECTEDVALUE(Data[Category],"error"),[Category Rank]=1)
 
However, I get the error "A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed."
 
Any help would be much appreciated!
 
Thanks,
Wesley
Need Help
Tips and Tricks

2 Replies

    • parry2k's avatar
      parry2k
      Icon for Super User rankSuper User
      6 years ago

      WesleyP8 try this measure

       

      Rank 1 category = CALCULATE(SELECTEDVALUE(Data[Category],"error"),
FILTER ( VALUES ( Data[Category] ), [Category Rank]=1)

      I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!