Skip to main content
cancel
Showing results for 
Search instead 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

Reply
CHOooo
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 MeasureIncorrect Measure

 

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

 

Desired output:

ChampionTotal Bans
Kallista199
Ashe194
Senna148

 

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_championdim_championTotal picks measureTotal picks measureTotal games measureTotal games measureModelModelfct_LPL_2024_Spring_Seasonfct_LPL_2024_Spring_SeasonPick RatePick Rate

 

1 ACCEPTED SOLUTION
v-rzhou-msft
Community Support
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.

vrzhoumsft_0-1713949148725.png

 

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.

 

View solution in original post

2 REPLIES 2
v-rzhou-msft
Community Support
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.

vrzhoumsft_0-1713949148725.png

 

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!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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