# How to make a slicer to filter for multiple columns

1 view
in BI

+----+---------+---------+-------+

| 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 can't do multiple selections

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

)

by (47.2k points)

1. Create two calculated columns.

con = CONCATENATE(Table1[Member1],Table1[Member2])

con2 = CONCATENATE(Table1[Member2],Table1[Member1])

2. Create two measures as below.

Conca = CALCULATE(CONCATENATEX(VALUES(Table1[Member1]),Table1[Member1]),ALLSELECTED(Table1))

Measure = IF(MAX(Table1[con]) = [Conca] || MAX(Table1[con2])= [Conca],1,0)

3. Then we can create the visual and filter the visual based on the Measure.