topic Re: Filtering on two selected values in DAX Commands and Tips

Filtering on two selected values

Anonymous — Tue, 16 Aug 2022 16:53:41 GMT

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.

Re: Filtering on two selected values

Greg_Deckler — Tue, 16 Aug 2022 17:02:56 GMT

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) ...

Re: Filtering on two selected values

Anonymous — Wed, 17 Aug 2022 12:36:50 GMT

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?

Re: Filtering on two selected values

Greg_Deckler — Wed, 17 Aug 2022 13:20:14 GMT

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

Re: Filtering on two selected values

v-jianboli-msft — Thu, 01 Sep 2022 05:55:40 GMT

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.