cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Combine multiple inactive relationships to filter fact table.

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:
Incorrect Measure

Dependancy of the SUMMARIZECOLUMNS 1st argument:
Unique Champion column table

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!

dim_championTotal picks measureTotal games measureModelfct_LPL_2024_Spring_SeasonPick Rate

1 ACCEPTED SOLUTION
Community Support

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.

2 REPLIES 2
Community Support

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.

Frequent Visitor

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!