Forum Discussion
Anonymous
9 years agoNot applicable
Slicer for value that could fall into multiple categories
I keep running into the same problem when I want to slicer for a column that could fall into multiple categories. If I want to find distinct values I use conditional columns "If (Contains". How ...
- 9 years ago
Probably a better way to do this, but here is one way.
Create a disconnected table with your categories:
Category
Blue Red Green Purple Yellow Next, create the following measures:
CategoryBlue = IF(SEARCH("Blue",MAXX(VALUES(Items[Category]),[Category]),1,BLANK()),1,0) CategoryGreen = IF(SEARCH("Green",MAXX(VALUES(Items[Category]),[Category]),1,BLANK()),1,0) CategoryRed = IF(SEARCH("Red",MAXX(VALUES(Items[Category]),[Category]),1,BLANK()),1,0) CategoryPurple = IF(SEARCH("Purple",MAXX(VALUES(Items[Category]),[Category]),1,BLANK()),1,0) CategoryYellow = IF(SEARCH("Yellow",MAXX(VALUES(Items[Category]),[Category]),1,BLANK()),1,0) MeasureSelected = IF ( HASONEVALUE ( Categories[Category] ), SWITCH ( FIRSTNONBLANK ( Categories[Category], Categories[Category] ), "Blue", [CategoryBlue], "Green", [CategoryGreen], "Purple", [CategoryPurple], "Red", [CategoryRed], "Yellow", [CategoryYellow] ), BLANK () )Put a Table or Matrix visualization with some column from your items, your Category column from your categories table and MeasureSelected. Filter the visual for MeasureSelected = 1. Add a slicer for your Category column from your categories table.
Vvelarde
Community Champion
9 years ago
Excellent idea. The only to solve in this is when you had multiple selections in Category.
v-jiascu-msft
Microsoft Employee
9 years ago
Hi,
That's right. If we selected more than one value, it will not only stop working, but also conduct misleading by 0.
Thank you!
Best Regards!
Dale