Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 do I make categories for values that will fall into multiple slices.
Say I want to filter on all values that contain "Blue" or all that contain "Green". They can be in any order. I will have 20 or more categories.
Thanks
Solved! Go to Solution.
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.
@Anonymous
Hi,
You have gotten many good ideas. All of them work great. I have tested them. There is another way you can try.
Measure = IF ( HASONEVALUE ( Categories[Category] ), IF ( SEARCH ( MIN ( Categories[Category] ), MIN ( Items[Category] ), 1, 0 ) = 0, 0, 1 ), 0 )
1 means match while 0 means the contrary.
Best Regards!
Dale
Excellent idea. The only to solve in this is when you had multiple selections in Category.
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
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.
Thanks everyone for the answers. The issue I have now is that some of my values contain the same wording as others. i.e. "Red" , "Red1", "Red2". Is there a way to search for distinct wording?
If your data is like how you displayed it originally, then just change "Red" to "Red ," in your search? Probably have problems with the last data point but you could potentially fix that in your query by simply adding a " ," to the end of the column. You could do that in DAX as well using CONCATENATE.
@Anonymous
For the First Part, To create a table with all your categories.
-Go to Query Editor
-In your Table, select the column with Categories
-Split Column by delimeter.
-Select Delimeter (Comma)
-Advance Options
-Split into Rows
-OK
Finally Home- Remove Rows -- Duplicate
@Anonymous
And Use a Measure :
Measure = IF ( HASONEVALUE ( Data[Product] ), SUMX ( ADDCOLUMNS ( CROSSJOIN ( VALUES ( Categories[Colors] ), VALUES ( Data[Colors-Available] ) ), "CROSS"; SEARCH ( Categories[Colors], Data[Colors-Available], 1; 0 ) ), [CROSS] ) )
And in Visual Level Filter select when is greater than 0.
User | Count |
---|---|
94 | |
83 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |