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