Dynamic filtering question

[mkrumpak]

Hi,

I would like to implement a slicer-like-control on a column that contains a list of values as shown in the example below, where item 1 has tags a, b, c, d, and e.

DATA SET

item      tag

1            a, b, c, d, e

2            a, b

3            a, b

4            b, c

5                                                      

The first ask is to generate a list of distinct values in column “tag”, so they can be displayed in the slicer filter control.

The second ask is to provide filtering functionality as follows:

Example 1) If tag “c” is selected then show {1, 4}

EXPECTED REPORT RESULT SET

item      tag

1            a, b, c, d, e

4            b, c

Example 2) If tags “c” and “b” are selected then show {1, 2, 3, 4}

EXPECTED REPORT RESULT SET

item      tag

1            a, b, c, d, e

2            a, b

3            a, b

4            b, c

Any suggestions for how to best implement this type of filtering in PowerBI?

[v-sihou-msft]

@mkrumpak

In this scenario, you can create a calculated table first to aggregate the tags together.

Table = SUMMARIZE(Tags,Tags[item],"tags",CONCATENATEX(Tags,Tags[tag],","))

Then build the relationship between two tables, and set "Both" direction filter.

Now you can filter the expected result based on tag selection.

Regards,

[mkrumpak]

Thank you for the reply.  

I am not able to create a calculated table that matches yours.  When I use yor formula  Table = SUMMARIZE(Tags,Tags[item],"tag",CONCATENATEX(Tags,Tags[tags],",")), I get a table that matches the original table.

When I create a table you described in the post manually, filtering works great!  Any chance you can elaborate on how to "unpivot" a row with a calculated Table?

[Anonymous]

You will want to use "Power Query" (edit queries) to split the fields out into many columns.... then "unpivot" them.  Both are easy to do in Edit Queries functionality.

1   a

1   b

1   c

...

2  a

2  b

3  a

...