Make a slicer to filter for multiple columns

Question

Please Help, I have this table {Table1}

+----+---------+---------+-------+
| ID | Member1 | Member2 | Score |
+----+---------+---------+-------+
|  1 | John    | Jack    |    10 |
|  2 | Jack    | John    |    20 |
|  3 | John    | John    |    30 |
|  4 | Jack    | Jack    |    40 |
|  5 | Sara    | Maya    |    50 |
|  6 | Jack    | Mia     |    60 |
|  7 | Maya    | John    |    70 |
+----+---------+---------+-------+

Is it possible that I have a slicer that for example when I do multiple selection like {John,Jack}

it will show me the rows that John and jack worked together on

Expected Result:

+----+---------+---------+-------+
| ID | Member1 | Member2 | Score |
+----+---------+---------+-------+
|  1 | John    | Jack    |    10 |
|  2 | Jack    | John    |    20 |
+----+---------+---------+-------+

So Far I have tried to create a custom table and put it in the slicer, but I cant do multiple selection

Members = 
DISTINCT (
    UNION (
        SELECTCOLUMNS ( Table1, "T", Table1[Member1] ),
        SELECTCOLUMNS ( Table1, "T", Table1[Member2] )
    )
)

Then I created this measure in Table 1, and filtered it to show value 1

ShowRow = 
IF (
    HASONEVALUE ( 'Members'[T] ),
    IF (
        COUNTROWS ( FILTER ( Table1, Table1[Member1] = VALUES ('Members'[T] ) ) )
            || COUNTROWS ( FILTER ( Table1, Table1[Member2] = VALUES ( 'Members'[T] ) ) ),

        1,
        0
    )
)

Answer 1

Thank you for the detailed description, your example was helpful in developing a solution. The following method should work:

• Create the Members table as you have done

  Members =     DISTINCT (
     UNION (
         SELECTCOLUMNS ( Table1, "T", Table1[Member1] ),
         SELECTCOLUMNS ( Table1, "T", Table1[Member2] )
     )    )
  

• Create two relationships between the tables. One between T and Member1, and the other between T and Member2. One of the relationships will be inactive, which is fine.

• Now create two measures to calculate whether the member is present in the respective column. We can select the relationship we want to use in each to achieve the desired result.

  InMember1 = SUMX(CALCULATETABLE(Table1,USERELATIONSHIP('Members'[T],Table1[Member2])),1)
  
  InMember2 = SUMX(CALCULATETABLE(Table1,USERELATIONSHIP('Members'[T],Table1[Member1])),1)
  

• Now create a final measure to calculate whether they are in either column

  InEither = IF([InMember1]+[InMember2]>0,1,0)
  

Here is a sample of what the final table looks like (sorry can't embed images yet).

You will notice the original Score column doesn't display for every row because of how the relationships work. I created a NewScore measure to solve this problem

NewScore = 
VAR Score1 = SUMX(CALCULATETABLE(Table1,USERELATIONSHIP('Members'[T],Table1[Member1])),Table1[Score])
VAR Score2 = SUMX(CALCULATETABLE(Table1,USERELATIONSHIP('Members'[T],Table1[Member2])),Table1[Score])
RETURN IF(ISBLANK(Score1),Score2,IF(ISBLANK(Score2),Score1,Score1))

I also want to link to this article as it may be helpful for future reference: https://www.sqlbi.com/articles/userelationship-in-calculated-columns/