Combine multiple inactive relationships to filter fact table.

[CHOooo]

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

[v-rzhou-msft · Accepted 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.

[CHOooo]

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!