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/

Answer 2

First, create a new table that contains all members (and do not create any relationships to it).

Members = DISTINCT(UNION(VALUES(Table2[Member1]), VALUES(Table2[Member2])))

(This will default to the column name Member1, but rename it to Member or T if you prefer.)

Now that we have this list, all we need to do is write a measure and use it as a filter.

ShowRows := IF(
                COUNTROWS(
                    INTERSECT(
                        VALUES('Members'[Member]),
                        {MAX(Table2[Member1]), MAX(Table2[Member2])}
                    )
                ) = 2, 1, 0 )

When you create a slicer using Members[Member], the VALUES function will return a list of which members you have selected. When you intersect this with Member1 and Member2 from the current row in your table (MAX is used to extract each value from the row context), you only get 2 if both members are in your slicer selection.

Now all you need to do is add the ShowRows measure to the visual level filter and set it to is 1.