Forum Discussion

lisam12's avatar
lisam12
New Member
4 years ago
Solved

Get value from one column based on expression in another column

Hi all, I have two tables. My Admin Group table is a list of administrators with different values (such as sales, rebates, etc.). I have assigned a score to each administrator in this table based on how well they are performing. On top of that, administrators are grouped into classes based on their membership group so that similarly sized administrators can be compared to each other. 

I now have a second table, a Leaderboard. The Leaderboard is a distinct list of the Class IDs from my Admin Group table. Now, I have pulled the lowest score for each administrator (lowest=best, in this case), but I would rather the function return the name of the administrator. Right now it is just returning the score. How do I fix this? 
The function I am using is: 

Class Leader = CALCULATE(MIN('Admin Groups'[Score]),FILTER('Admin Groups','Admin Groups'[Class ID]=EARLIER('Leaderboard'[Class ID])))
Result:


These numbers are correct. They are the lowest score for each class. However, I have tried changing to formula to: Class Leader = CALCULATE(MIN('Admin Groups'[Admin FullName]),FILTER('Admin Groups','Admin Groups'[Class ID]=EARLIER('Leaderboard'[Class ID])))
However, this just returns the alphabetic first within each class. 

How do I get the Admin FullName field that corresponds to the correct lowest score that is returned in the first function?

  • lisam12 , Try a new column like

     

    measure =
    var _score = CALCULATE(MIN('Admin Groups'[Score]),FILTER('Admin Groups','Admin Groups'[Class ID]=EARLIER('Leaderboard'[Class ID])))
    return
    CALCULATE(MIN('Admin Groups'[Admin FullName]),FILTER('Admin Groups','Admin Groups'[Class ID]=EARLIER('Leaderboard'[Class ID])
    && 'Admin Groups'[Score] = _score))

2 Replies

  • lisam12 , Try a new column like

     

    measure =
    var _score = CALCULATE(MIN('Admin Groups'[Score]),FILTER('Admin Groups','Admin Groups'[Class ID]=EARLIER('Leaderboard'[Class ID])))
    return
    CALCULATE(MIN('Admin Groups'[Admin FullName]),FILTER('Admin Groups','Admin Groups'[Class ID]=EARLIER('Leaderboard'[Class ID])
    && 'Admin Groups'[Score] = _score))

    • lisam12's avatar
      lisam12
      New Member

      Worked perfectly! Thank you!