Forum Discussion
Dynamic filtering question
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?
3 Replies
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,
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?
