Slicer with overlapping group

[wongzqn]

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.

ProjectCode

A100Y

A123T

A123P

B009X

B123T

B123M

C777N

C123M

C997Y

Selecting 'A' returns:

A100Y

A123T

A123P

Selecting '123T' returns:

A123T

B123T

Thank you.

[v-xuding-msft · Accepted solution]

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 )

[wongzqn]

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

[wongzqn]

Hi @v-xuding-msft ,

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

[v-xuding-msft]

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.

[wongzqn]

Hi Xue Ding @v-xuding-msft,

I actually have a data table like this:

Date	Donor Name	Project Code	Donation Amount
6-Jan-2019	ABC	A100Y	100
8-Feb-2019	XYZ	A123P	200
5-Aug-2019	POE	C777N	100
6-Dec-2019	IOP	A123P	400
4-Jan-2020	LOP	B009X	100
5-Mar-2020	POE	B123T	500
6-Apr-2020	MOO	A997Y	300

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]

@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

________________________

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

[amitchandak]

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