Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

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 ...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    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.

     

     

     

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
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's avatar
Vvelarde
Icon for Community Champion rankCommunity Champion
9 years ago

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's avatar
    Vvelarde
    Icon for Community Champion rankCommunity Champion
    9 years ago

    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.