Slicer for value that could fall into multiple categories

[Anonymous]

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

[v-jiascu-msft]

@Anonymous

Hi,

You have gotten many good ideas. All of them work great. I have tested them. There is another way you can try.

1. Create a Categories table with all and unique colors.
2. Create a measure with this formula.

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

[Vvelarde]

@v-jiascu-msft

Excellent idea. The only to solve in this is when you had multiple selections in Category.

[v-jiascu-msft]

@Vvelarde

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

[Greg_Deckler · Accepted 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]

@Greg_Deckler

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?

[Greg_Deckler]

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.

[Vvelarde]

@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

[Vvelarde]

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