Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
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?
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
...
User | Count |
---|---|
124 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |