Forum Discussion

Chris154's avatar
Chris154
Frequent Visitor
2 years ago
Solved

Categorize with measure based on table range

Hi team,

 

I am trying to create a leaderboard where our employees can reach new levels based on the number of issues they have solved.

For this I have two tables: 

1) Issue log, which delivers the number of Issues closed by the team members

2) Level Overview, which defines at which step a new level is unlocked

 

As you can see from the attached file, I am stuck at the point that I can just lookup the exact value.

I somehow need to compare the actual number of issues closed to the Level Overview table and find the matching range (level).

I used the following formula, but I don't know how to modify it to find the correct range:

LevelAchieved = LOOKUPVALUE('Level Overview'[Level],'Level Overview'[Issues completed],'Issue Log'[Issues closed])

 

 

Any guidance would be much appreciated.

 

 

  • Irwan's avatar
    Irwan
    2 years ago

    hello Chris154 ,

     

    i am a beginner but is this result match to your need?

     

    Table 2 is your reference table:

     

    Hope this will give you some help.

    Thank you.

3 Replies

  • Irwan's avatar
    Irwan
    Super User
    2 years ago

    hello Chris154 ,

     

    i am a beginner but is this result match to your need?

     

    Table 2 is your reference table:

     

    Hope this will give you some help.

    Thank you.

    • Chris154's avatar
      Chris154
      Frequent Visitor
      2 years ago

      Hi Irwan ,

       

      thanks so much! That was what I needed.

      It did give me a bit of a headache afterwards, as my Level names are not "Level 1", "Level 2" etc. but fun words like "Curious Carrot" etc.

      I had the issue, that it would return me the maximum value alphabetical wise instead of the corresponding Level name to the maximum achieved Issues completed.

       

      I fixed this by then using the result in a lookupvalue formula:

      Current Level = LOOKUPVALUE('Level Overview'[Level],'Level Overview'[Issues completed], MAXX(FILTER('Level Overview','Level Overview'[Issues completed]<='Issue Log'[Issues closed]),'Level Overview'[Issues completed]))

       

      Just posting this if anyone else runs into a similar issue with this.

       

      Again, thank you very much!