cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Cutter with overlapping group

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
Highlighted
Super User IX
Super User IX

Re: Slicer with overlapping group

@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))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Community Champion
Community Champion

Re: Slicer with overlapping group

@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

 

Highlighted
Community Support
Community Support

Re: Cutter with overlapping group

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

Highlighted
Frequent Visitor

Re: Cutter with overlapping group

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

Highlighted
Frequent Visitor

Re: Cutter with overlapping group

Hi @v-xuding-msft ,

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

Highlighted
Community Support
Community Support

Re: Cutter with overlapping group

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.
Highlighted
Frequent Visitor

Re: Cutter with overlapping group

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.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors