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

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

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:

ChampionBlind Pick %
Aatrox44%
Ahri58%
Akali26%


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! 😁


ModelModelBP% Measure p1BP% Measure p1BP% Measure p2BP% Measure p2BP% Query View p1BP% Query View p1BP% Query View p2BP% Query View p2

 

1 ACCEPTED SOLUTION
v-zhouwen-msft
Community Support
Community Support

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

vzhouwenmsft_0-1716805195107.png

vzhouwenmsft_1-1716805214073.png

 

View solution in original post

2 REPLIES 2
v-zhouwen-msft
Community Support
Community Support

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

vzhouwenmsft_0-1716805195107.png

vzhouwenmsft_1-1716805214073.png

 

Hey @v-zhouwen-msft ,

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





Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors