Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Qyntius
Frequent Visitor

LOOKUP-multiple-VALUES to perform aggregations on

I have a fact table containing player performance data of their matches in League of Legends. I want to be able to display the average stats of a specific player by their username/position and compare/relate those stats to their opponents and teammates.

 

This specific data set only contains my own matches (with my own stats being the target of the analysis), but ideally, I'd find a way to find these stats in larger data sets also containing matches that don't include the subject I want to analyze. 

 

The way it works

  • The players are put into random teams each match
    • I created an unique teamId by merging MatchId and team-color
  • There are five players per team and each has its own unique position/role/lane per team.
    • Referred to as lane (Top, Jungle, Mid, Bot, Support)
  • The players do hold on to their Username between games
    • Referred to as Summonername
  • I created an unique participantId by merging the matchId + team color + position

Dataset references

Qyntius_2-1668255884251.png

Qyntius_3-1668255945784.png

 

Attempt 1:

avg enemy CS = 
CALCULATE(
    AVERAGEX(
        FILTER(Players, Players[Lane] == "Mid"), [Total.MinionsKilled]),
    ALL(Players),
    FILTER(Players, Players[summonerName] <> "Qyntius")
)

This only works if the dataset is limited to matches that have "Qyntius" present in the match and playing Mid specifically. Unfortunately, there are also matches where this is not the case.  This means I'd have to filter the rows of the entire dataset to the matches played in a single lane, but I also want to be able to compare the players performance on different lanes with each other. Now that I'm typing this, I should create a table for each lane the target played? But that seems messy with relationships right?

 

Attempt 2:

Rival Gold = 
VAR team =
    LOOKUPVALUE ( Players[TeamId], [summonerName], "Qyntius" )
VAR lane =
    LOOKUPVALUE ( Players[Lane], [summonerName], "Qyntius" )
VAR match =
    LOOKUPVALUE ( Players[match_id], [Summonername], "Qyntius" )
RETURN
    CALCULATE (
        AVERAGE ( Players[Total.Gold] ),
        FILTER ( players, Players[match_id] = match ),
        FILTER ( players, Players[Lane] = lane ),
        FILTER ( players, Players[TeamId] <> team )
    )

I think this is what I want, but LOOKUPVALUE can only return a single value. I want  LOOKUPVALUE to "build" a new table of direct rivals (same position, same match, different team), if that makes sense. It seems like something of a loop is needed to achieve this.

 

I am also looking into creating a calculated column to "tag" all players with either "ally", "enemy", or "nomatch" in relation to the player I want to analyze, but running into the same issues. Worst case scenario I can always try to add this column at the data sourcing process as the table is built match by match, but for learning's sake, I'm trying to find a way to do it in Power BI.

 

I apologize if this question is silly and for the wall of text,  but I am stumped in trying to figure this out by myself. This is my first project. I downloaded PowerBI a little over two weeks ago and I have no experience in DA/coding so bear with me. 

2 REPLIES 2
FreemanZ
Super User
Super User

Looks interesting and also complicated. I would suggest you to break down and abstract your issues and try to solve it one after another. 

Yeah, I'm definitely in the process of doing this. I just posted ahead of time because I'm hoping there is some simple function or mechanic that I'm overseeing. Even just the affirmation that this might be complicated is appreciated as it implies I might not be overthinking it, so thanks!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors