Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Dataset references
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.
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!
User | Count |
---|---|
40 | |
26 | |
22 | |
16 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |