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

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.

     

     

     

8 Replies

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

         

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

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

        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.

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

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

      v-jiascu-msft

       

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

       

       

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        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