Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I am trying to calculate the ban count of each champion that has been represented in all games played in the League of Legends Chinese pro league.
Context on the dataset:
The dataset contains esports data for the Chinese LOL pro league, where each team plays 16 bo3 games during the regular season to qualify for playoffs.
A GameID represents a unique game played, SeriesID represents the unique series played and Game Number is the game number within a series, ranging from 1 to 3.
Each team bans 5 champions that cannot be picked during the pick phase of the current game (represented across 5 columns: ban1 to ban5).
With the way the dataset is structured, a ban will be represented 6x per single GameID, this is because each row in the dataset represents a player or a team in the participantID column (participant 1-5 is blue side, 6-10 red side, 100 is blue team and 200 is red team)
For example: Looking at seriesID = 1, Akali was banned 3 times out of 3 games.
How can I calculate the ban rate of each champion across the entire table for each champion that was banned during the competition?
Ive tried using TREATAS, to create a virtual relationship between the dim_Champion table, with the 5 ban columns in the main fact table, but that has not worked out. Shown below:
Dependancy of the SUMMARIZECOLUMNS 1st argument:
Desired output:
Champion | Total Bans |
Kallista | 199 |
Ashe | 194 |
Senna | 148 |
Further context: The correct ban numbers are on column 4 in the table on this webpage:
https://lol.fandom.com/wiki/LPL/2024_Season/Spring_Season/Champion_Statistics
Ive attached the pbix file in this dropbox link below:
PBIX File
Thanks!
Solved! Go to Solution.
Hi @CHOooo ,
I update your "Five Column" table as below.
DimBan =
DISTINCT(
UNION(
SELECTCOLUMNS(LPL_Spring_Season_2024, "DimBan", LPL_Spring_Season_2024[ban1])
, SELECTCOLUMNS(LPL_Spring_Season_2024, "DimBan", LPL_Spring_Season_2024[ban2])
, SELECTCOLUMNS(LPL_Spring_Season_2024, "DimBan", LPL_Spring_Season_2024[ban3])
, SELECTCOLUMNS(LPL_Spring_Season_2024, "DimBan", LPL_Spring_Season_2024[ban4])
, SELECTCOLUMNS(LPL_Spring_Season_2024, "DimBan", LPL_Spring_Season_2024[ban5])
)
)
Then you can try code as below to create a measure.
Measure =
CALCULATE (
DISTINCTCOUNT ( LPL_Spring_Season_2024[GameID] ),
FILTER (
ALLSELECTED(LPL_Spring_Season_2024),
LPL_Spring_Season_2024[ban1] = MAX(DimBan[DimBan])
|| LPL_Spring_Season_2024[ban2] = MAX(DimBan[DimBan])
|| LPL_Spring_Season_2024[ban3] = MAX(DimBan[DimBan])
|| LPL_Spring_Season_2024[ban4] = MAX(DimBan[DimBan])
|| LPL_Spring_Season_2024[ban5] = MAX(DimBan[DimBan])
)
)
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @CHOooo ,
I update your "Five Column" table as below.
DimBan =
DISTINCT(
UNION(
SELECTCOLUMNS(LPL_Spring_Season_2024, "DimBan", LPL_Spring_Season_2024[ban1])
, SELECTCOLUMNS(LPL_Spring_Season_2024, "DimBan", LPL_Spring_Season_2024[ban2])
, SELECTCOLUMNS(LPL_Spring_Season_2024, "DimBan", LPL_Spring_Season_2024[ban3])
, SELECTCOLUMNS(LPL_Spring_Season_2024, "DimBan", LPL_Spring_Season_2024[ban4])
, SELECTCOLUMNS(LPL_Spring_Season_2024, "DimBan", LPL_Spring_Season_2024[ban5])
)
)
Then you can try code as below to create a measure.
Measure =
CALCULATE (
DISTINCTCOUNT ( LPL_Spring_Season_2024[GameID] ),
FILTER (
ALLSELECTED(LPL_Spring_Season_2024),
LPL_Spring_Season_2024[ban1] = MAX(DimBan[DimBan])
|| LPL_Spring_Season_2024[ban2] = MAX(DimBan[DimBan])
|| LPL_Spring_Season_2024[ban3] = MAX(DimBan[DimBan])
|| LPL_Spring_Season_2024[ban4] = MAX(DimBan[DimBan])
|| LPL_Spring_Season_2024[ban5] = MAX(DimBan[DimBan])
)
)
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Morning @v-rzhou-msft ,
Thank you so much for the response, it works as intended.
I am going to play around with the solution, so that the table has the same data lineage as the Champion table, allowing me to filter by Champion[Champion].
Thanks again!
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |