Get max value from a column in a related table

[KentY]

Hello all,

New to PowerBI, and am trying to follow the best practice of building a model rather than a report. I know this question has been asked in various forms elsewhere in these forums, but I can't quite wrap my head around how to apply those solutions to my particular situation, so I apologize in advance if this question is a duplication of those questions.

Anyway, as a lifelong gamer, I have a fun project I'm doing where I'm creating a gamification system for my work (I'm not expecting anything super impactful from this project, it's just something fun to try).

What I have is a 'LevelXP-Team' table that has the XP requirements to reach each level:

This 'LevelXP-Team' table is then related Many-to-One to a 'TeamMemberRef' table that contains each team member and their Total XP:

What I would like to do is create 2 new columns in 'TeamMemberRef' with these respective values:

1. 'TeamMemberRef'[CurrentLevel]
   • Return the corresponding level from 'LevelXP-Team'[Level] that the user has reached, using the 'LevelXP-Team'[(Max Level 99)] column as the XP requirement.
2. 'TeamMemberRef'[XPToNextLevel]
   • Return the remaining XP from 'LevelXP-Team'[(Max Level 99)] that they have left to reach the next level in 'LevelXP-Team'[Level]

So for example, if John Doe in the TeamMemberRef table has 5,000 XP (so a value of 5,000 in the 'TeamMemberRef'[TotalXP-Team] column), I would want to return in a new "CurrentLevel" column that his current level is 5 (going off the 'LevelXP-Team'[(Max Level 99)] column for the XP requirement), and then I would want a new separate "XPToNextLevel" column that shows he has 751 XP left to reach the next level (which would be Level 6 with a requirement of 5,751 XP).

Thanks!

[jairoaol]

you should not do that by relationships but by using column-level DAX calculations.

if the TotalXP-Team field is between MAX periods then assign it a required XP level.

[KentY]

Thank you for your reply. Could you please give an example of the DAX expressions I would use, or the steps I would follow? I'm new to PowerBI so I'm not quite sure where to start. Thanks again!

[KentY · Accepted solution]

Did more looking online, and was able to solve it. Here are my final DAX expressions. Not sure if they are "optimal" but they work for me!

1. 'TeamMemberRef'[CurrentLevel]
   • Return the corresponding level from 'LevelXP-Team'[Level] that the user has reached, using the 'LevelXP-Team'[(Max Level 99)] column as the XP requirement.
     • CurrentLevel = LOOKUPVALUE('LevelXP-Team'[Level],'LevelXP-Team'[XP (Max Level 99)],CALCULATE(MAX('LevelXP-Team'[XP (Max Level 99)]),FILTER(ALL('LevelXP-Team'),TeamMemberRef[TotalXP-Team]>='LevelXP-Team'[XP (Max Level 99)])))
2. 'TeamMemberRef'[XPToNextLevel]
   • Return the remaining XP from 'LevelXP-Team'[(Max Level 99)] that they have left to reach the next level in 'LevelXP-Team'[Level]
     • XPToNextLevel = CALCULATE(MIN('LevelXP-Team'[XP (Max Level 99)]),FILTER(ALL('LevelXP-Team'),TeamMemberRef[TotalXP-Team]<'LevelXP-Team'[XP (Max Level 99)])) - TeamMemberRef[TotalXP-Team]