Forum Discussion
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.
ProjectCode |
| A100Y |
| A123T |
| A123P |
| B009X |
| B123T |
| B123M |
| C777N |
| C123M |
| C997Y |
Selecting 'A' returns:
| A100Y |
| A123T |
| A123P |
Selecting '123T' returns:
| A123T |
| B123T |
Thank you.
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 )
7 Replies
- amitchandak
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))
- 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 🙂
v-xuding-msftCommunity 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 )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?
