Back

Explore Courses Blog Tutorials Interview Questions
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

0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
asked Nov 30, 2020 in BI by Chris (11.1k points)
0 votes
1 answer
asked Dec 3, 2020 in BI by Chris (11.1k points)
Welcome to Intellipaat Community. Get your technical queries answered by top developers!

28.4k questions

29.7k answers

500 comments

94k users

Browse Categories

...