Unable to slice values from virtual tables - Context transition or data lineage issue.

[CHOooo]

Hello,

I've recently encountered an issue with a measure (Blind Pick %) I created that compares the values of a virtual column against a row's counterpart.

It works as intended when I view the results in DAX Query view, but when I transform it into a measure to return a scalar, I cannot slice it by the intended dimension (in this case 

LPL_Spring_Season_2024[champion]).

I am not sure if it is a context transition issue or if it is a data lineage one - I've been leaning towards the former, but I'm not sure how I can wrap calculate within a SWITCH statement to force the context transition.

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.
There are 324 unique gameids in the dataset. Each gameid contains 12 rows, 5 for each player and 1 for each team

Each team has 5 players, represented as a participantid within a gameid - ranging from 1-10. Each number indicates what role a player plays in.
1-5 = Players on the blue side for that gameid. 100 = Blue team
6-10 = Players on the red side for that gameid. 200 = Red team.

1 & 6 = Top Lane
2 & 7 = Jungle
3 & 8 = Mid
4 & 9 = ADC 
5 & 10 = Support

Before a game starts, there is a draft phase, where both teams ban champions that cannot be picked for the game and pick champions for each player in a turn-based manner.

Here is the order of selection for the pick phase for each game:
Blue Team 1st Pick: (1)
Red Team 1st & 2nd Pick: (2)
Blue Team 2nd & 3rd Pick: (3)
Red Team 3rd & 4th Pick: (4)
Blue Team 4th & 5th Pick: (5)
Red Team 5th Pick: (6)

After the pick phase is concluded, the teams will swap the champions so that they are played by the appropriate player.

A blind pick is when a player picks a champion prior to their counterpart picking a champion.
For example, in gameid = 1, comparing the champions picked in top lane, K'sante was picked prior to Udyr, so K'sante for that game is considered to be blind picked.

Desired Output:

Champion	Blind Pick %
Aatrox	44%
Ahri	58%
Akali	26%

Ive attached the pbix file in this dropbox link below:
PBIX File

(I've been using the "Number Table Debug" measure in TB3 to try and debug the issue. It is just the Blind pick % measure, but only containing the 1st virtual table and counting how many rows have a [Pick Order] = 1)

Thanks! 😁

ModelBP% Measure p1BP% Measure p2BP% Query View p1BP% Query View p2

 

[Anonymous · Accepted solution]

Hi @CHOooo ,

Use the following DAX expression to create a table

Table = 
  VAR _table1 = ADDCOLUMNS(
        LPL_Spring_Season_2024,
        "Pick Order",
        VAR Current_GameID = LPL_Spring_Season_2024[GameID]
        VAR Filtered_Table = 
            FILTER(
                LPL_Spring_Season_2024,
                LPL_Spring_Season_2024[GameID] = Current_GameID
            )
        VAR _a = 'LPL_Spring_Season_2024'[participantid]    
        RETURN
        SWITCH(
            TRUE(),
            CONTAINS(Filtered_Table, LPL_Spring_Season_2024[pick1], LPL_Spring_Season_2024[champion]) && LPL_Spring_Season_2024[participantid] <= 5, 1,
            CONTAINS(Filtered_Table, LPL_Spring_Season_2024[pick2], LPL_Spring_Season_2024[champion]) && LPL_Spring_Season_2024[participantid] <= 5, 3,
            CONTAINS(Filtered_Table, LPL_Spring_Season_2024[pick3], LPL_Spring_Season_2024[champion]) && LPL_Spring_Season_2024[participantid] <= 5, 3,
            CONTAINS(Filtered_Table, LPL_Spring_Season_2024[pick4], LPL_Spring_Season_2024[champion]) && LPL_Spring_Season_2024[participantid] <= 5, 5,
            CONTAINS(Filtered_Table, LPL_Spring_Season_2024[pick5], LPL_Spring_Season_2024[champion]) && LPL_Spring_Season_2024[participantid] <= 5, 5,
            CONTAINS(Filtered_Table, LPL_Spring_Season_2024[pick1], LPL_Spring_Season_2024[champion]) && LPL_Spring_Season_2024[participantid] < 11, 2,
            CONTAINS(Filtered_Table, LPL_Spring_Season_2024[pick2], LPL_Spring_Season_2024[champion]) && LPL_Spring_Season_2024[participantid] < 11, 2,
            CONTAINS(Filtered_Table, LPL_Spring_Season_2024[pick3], LPL_Spring_Season_2024[champion]) && LPL_Spring_Season_2024[participantid] < 11, 4,
            CONTAINS(Filtered_Table, LPL_Spring_Season_2024[pick4], LPL_Spring_Season_2024[champion]) && LPL_Spring_Season_2024[participantid] < 11, 4,
            CONTAINS(Filtered_Table, LPL_Spring_Season_2024[pick5], LPL_Spring_Season_2024[champion]) && LPL_Spring_Season_2024[participantid] < 11, 6,
            BLANK()
        )
    )
 VAR _table2 = ADDCOLUMNS(_table1,"column",SWITCH(TRUE(),[participantid] = 100 || [participantid] = 200 ,BLANK(),[participantid] <=5 ,[participantid] + 5 , [participantid] - 5))
 VAR _table3 = ADDCOLUMNS(_table2,"Pick Order 2 ",
          VAR _a = [GameID]
          VAR _b = [column]
          VAR _c = FILTER(_table2,[GameID] = _a && [participantid] = _b )
          RETURN MAXX(_c,[Pick Order]))
RETURN ADDCOLUMNS(_table3,"column3",IF(ISBLANK([Pick Order]),BLANK(),IF([Pick Order] < [Pick Order 2 ],1,0)))

Use the following DAX expression to create a measure

Measure = 
VAR _a = SELECTEDVALUE(Champion[Champion])
VAR _b = COUNTROWS(FILTER('Table','Table'[column3] = 1 && 'Table'[champion] = _a))
RETURN DIVIDE(_b,[Total Picks])

Final output

[CHOooo]

Hey @Anonymous ,

Thanks for the response, I really appreciate you making the counterpart portion of the code shorter and cleaner.