Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mkrumpak
Regular Visitor

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 3
v-sihou-msft
Employee
Employee

@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],","))

9.PNG

 

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

 

8.PNG

 

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

 

777.PNG

 

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?

 

 

Anonymous
Not applicable

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

...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.