CODEWITHSUNDEEP
powerbidax
sara
sara

Reputation: 534

Make a slicer to filter for multiple columns

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

Upvotes: 4

Views: 8625

Answers (2)

Alexis Olson
Alexis Olson

Reputation: 40204

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.

Upvotes: 0

Stu
Stu

Reputation: 209

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.

Here is what the relationship should look like

  • 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).

Click here to view image.

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/

Upvotes: 3

Related Questions