Filtering on two selected values

[Anonymous]

Hi, All!

I do really not know if I am looking at this task from the right angel, so I am really hoping one of you can help. 

My goal is, that I would like to select one team in the tabel to the left, then select an opponent in the slicer in the top right, and then get their matchhistory against eachother in the table to the right. 

My initial thought was, that I needed to collect the selected value in the slicer, and then filter on whether both the selected teams are included in the Game ID, but I truly do not know if this is the right way to handle it.

My dataset looks like the below, where there is multiple rows with the same Game ID with different information in each column. 

Any suggestions to solve this matter are kindly received. 

Thanks in advance. 

[v-jianboli-msft · Accepted solution]

Hi @Anonymous ,

Based on your description, I have created a simple sample:

Please try:

First, create a new table of all the teams for slicer:

Then creata a measure:

Flag =
IF (
    MAX ( 'Table'[Winning Team] )
        IN SELECTCOLUMNS ( 'For Slicer', "Team", [Team] )
            && MAX ( 'Table'[Losing Team] ) IN SELECTCOLUMNS ( 'For Slicer', "Team", [Team] ),
    1,
    0
)

Add it to the filter:

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@Anonymous I would recommend 2 disconnected tables for your slicers. Both would be built using DISTINCT('Table'[teamname]). Then you could get the gameid's by doing something like:

Measure = 
  VAR __TeamA = SELECTEDVALUE('Slicer1'[teamname])
  VAR __TeamB = SELECTEDVALUE('Slicer2'[teamname])
  VAR __TeamAGames = DISTINCT(SELECTCOLUMNS(FILTER('Table',[teamname] = __TeamA),"__gameid",[gameid]))
  VAR __TeamBGames = DISTINCT(SELECTCOLUMNS(FILTER('Table',[teamname] = __TeamB),"__gameid",[gameid]))
  VAR __SharedGames = INTERSECT(__TeamAGames, __TeamBGames)
...

  

[Anonymous]

@Greg_Deckler  Thanks for your answer. 

I do kinda follow where you are going, however, I got no idea what to put into the return section of the measure, as I would like to display a table with all the gameid? - I do not see how I can filter the data tabel based on _SharedGames varaible?

[Greg_Deckler]

@Anonymous Yeah, I wasn't sure either! But, perhaps you could use this code in a Complex Selector. Essentially return 1 or 0 and use the Filter pane to filter to the 1's (the rows you want included in the visual). So like:

Measure = 
  VAR __Game = MAX('Table'[gameid])
  VAR __TeamA = SELECTEDVALUE('Slicer1'[teamname])
  VAR __TeamB = SELECTEDVALUE('Slicer2'[teamname])
  VAR __TeamAGames = DISTINCT(SELECTCOLUMNS(FILTER('Table',[teamname] = __TeamA),"__gameid",[gameid]))
  VAR __TeamBGames = DISTINCT(SELECTCOLUMNS(FILTER('Table',[teamname] = __TeamB),"__gameid",[gameid]))
  VAR __SharedGames = INTERSECT(__TeamAGames, __TeamBGames)
RETURN
  IF(__Game IN __SharedGames, 1, 0)

The Complex Selector - Microsoft Power BI Community