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
wongzqn
Frequent Visitor

Slicer with overlapping group

Hi, I have a column of project codes which I would like to have a slicer with the option of: 'A', 'B', 'C', '123T', '123Y', where 'A', 'B', 'C' grouping is based on the starting letter of the project code and '123T' and '123Y' based on matching string within the project code. The grouping of project codes are not mutually exclusive.

 

I want to achieve somthing like this in my dashboard.

Screenshot 2020-09-29 172837.png

 

ProjectCode

A100Y
A123T
A123P
B009X
B123T
B123M
C777N
C123M
C997Y

 

Selecting 'A' returns:

A100Y
A123T
A123P

 

Selecting '123T' returns:

A123T
B123T

 

Thank you.

1 ACCEPTED SOLUTION
v-xuding-msft
Community Support
Community Support

Hello @wongzqn ,

Please try this:

Create a new disconnected table as a slicer:

Slicer = 
UNION (
    DISTINCT ( SELECTCOLUMNS ( 'Table', "ABC", LEFT ( 'Table'[ProjectCode], 1 ) ) ),
    SELECTCOLUMNS (
        'Table',
        "xxx", RIGHT ( 'Table'[ProjectCode], LEN ( 'Table'[ProjectCode] ) - 1 )
    )
)

Create a measure:

Measure =
VAR selected =
    SELECTEDVALUE ( Slicer[ABC] )
VAR contain =
    SEARCH ( selected, MAX ( 'Table'[ProjectCode] ),, 999 )
RETURN
    IF ( ISFILTERED ( Slicer[ABC] ), contain )

2.gif

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
v-xuding-msft
Community Support
Community Support

Hello @wongzqn ,

Please try this:

Create a new disconnected table as a slicer:

Slicer = 
UNION (
    DISTINCT ( SELECTCOLUMNS ( 'Table', "ABC", LEFT ( 'Table'[ProjectCode], 1 ) ) ),
    SELECTCOLUMNS (
        'Table',
        "xxx", RIGHT ( 'Table'[ProjectCode], LEN ( 'Table'[ProjectCode] ) - 1 )
    )
)

Create a measure:

Measure =
VAR selected =
    SELECTEDVALUE ( Slicer[ABC] )
VAR contain =
    SEARCH ( selected, MAX ( 'Table'[ProjectCode] ),, 999 )
RETURN
    IF ( ISFILTERED ( Slicer[ABC] ), contain )

2.gif

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you @v-xuding-msft , this works for me! 🙂

Hi @v-xuding-msft ,

Do you know why some visuals like Card/Pie chart are not responding to this slicer?

Hi @wongzqn ,

 

The formula returns more than one text value. You could use multi-row card rather than card to show the results. And for pie chart, it can show number values, like summarized values, count or average values. So you need to change the formula based on your requirement.

3.gif

 

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Xue Ding @v-xuding-msft,

I actually have a data table like this:

DateDonor NameProject CodeDonation Amount
6-Jan-2019ABCA100Y100
8-Feb-2019XYZA123P200
5-Aug-2019POEC777N100
6-Dec-2019IOPA123P400
4-Jan-2020LOPB009X100
5-Mar-2020POEB123T500
6-Apr-2020MOOA997Y300

When I summarize the the YTD Donation into a single card visual, it is not responsive to the slicer.

And also a pie chart on corporate donor count by project code, it is not responsive either.

So I'm not sure what went wrong or needs to be fixed.

Fowmy
Super User
Super User

@wongzqn 

To achieve your results, you need to create an additional table that splits the codes into two and combine, which I did in Power Query, then create a measure that identifies the selected slicer value with the relevant code. Finally, you can assign it to the visual slicer for your table and it filters as you select.

You can download the file: HERE



Fowmy_0-1601376243494.png

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

amitchandak
Super User
Super User

@wongzqn , Try like

// One value at time

calculate(count(table[ProjectCode]), filter(all(Table), search(selectevalue(Table[ProjectCode]) ,Table[ProjectCode],1,0)>0))

 

//better to have ProjectCode in an independent Table

calculate(count(table[ProjectCode]), filter(Table, search(selectevalue(Code[ProjectCode]) ,Table[ProjectCode],1,0)>0))

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.