Categorize with measure based on table range

[Chris154]

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 · Accepted solution]

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]

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!

[Irwan]

Glad to be a help.

Thank you.