0 votes
1 view
in BI by (17.6k points)

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

    )

1 Answer

0 votes
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.

Related questions

Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...