Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Creating a slicer that groups together values from a column

Hi,

I am trying to create a slicer that groups together various items in one column from an exisiting table.

For example, if my existing table contains a column "Food" like so:

Food
Strawberries
Carrots
Cucumbers
Cake
Banana
Orange

I would like to create a slicer with 3 options, Fruit, Vegetables and Dessert, where Fruit filters my dashboard to rows containing "Strawberries", "Banana" and "Orange, Vegetables filters to rows containing "Carrots" and "Cucumbers", and Desserts filters to rows containing "Cake".

 

Thanks

Need Help
  • PaulDBrown's avatar
    PaulDBrown
    4 years ago

    You could add a conditional column in Power query for a new column

    OR

    create a new table to use as the slicer using:

     

     

     

    Slicer =
VAR Dessert =
    ADDCOLUMNS (
        CALCULATETABLE ( VALUES ( OriginalTable[Food] ), OriginalTable[Food] = "Cake" ),
        "Type", "Dessert"
    )
VAR Vegetables =
    ADDCOLUMNS (
        CALCULATETABLE (
            VALUES ( OriginalTable[Food] ),
            OriginalTable[Food] IN { "Cucumbers", "Carrots" }
        ),
        "Type", "Vegetables"
    )
VAR Fruit =
    ADDCOLUMNS (
        EXCEPT (
            VALUES ( OriginalTable[Food] ),
            UNION (
                CALCULATETABLE ( VALUES ( OriginalTable[Food] ), OriginalTable[Food] = "Cake" ),
                CALCULATETABLE (
                    VALUES ( OriginalTable[Food] ),
                    OriginalTable[Food] IN { "Cucumbers", "Carrots" }
                )
            )
        ),
        "Type", "Fruit"
    )
RETURN
    UNION ( Dessert, Vegetables, Fruit )

     

     

     

     

    Or Create a copy of the table (Modeling -> New Table) and type Slicer = 'OriginalTable'
    Then 

     

    Then create a 1:* relationship between the food fields in both tables. 

     

2 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    4 years ago

    You could add a conditional column in Power query for a new column

    OR

    create a new table to use as the slicer using:

     

     

     

    Slicer =
VAR Dessert =
    ADDCOLUMNS (
        CALCULATETABLE ( VALUES ( OriginalTable[Food] ), OriginalTable[Food] = "Cake" ),
        "Type", "Dessert"
    )
VAR Vegetables =
    ADDCOLUMNS (
        CALCULATETABLE (
            VALUES ( OriginalTable[Food] ),
            OriginalTable[Food] IN { "Cucumbers", "Carrots" }
        ),
        "Type", "Vegetables"
    )
VAR Fruit =
    ADDCOLUMNS (
        EXCEPT (
            VALUES ( OriginalTable[Food] ),
            UNION (
                CALCULATETABLE ( VALUES ( OriginalTable[Food] ), OriginalTable[Food] = "Cake" ),
                CALCULATETABLE (
                    VALUES ( OriginalTable[Food] ),
                    OriginalTable[Food] IN { "Cucumbers", "Carrots" }
                )
            )
        ),
        "Type", "Fruit"
    )
RETURN
    UNION ( Dessert, Vegetables, Fruit )

     

     

     

     

    Or Create a copy of the table (Modeling -> New Table) and type Slicer = 'OriginalTable'
    Then 

     

    Then create a 1:* relationship between the food fields in both tables. 

     

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous , Create  a new column and use that in the slicer

     

    Switch( True() ,

    [food] in {"Strawberries", "Banana" ,"Orange"} , "Fruit",

    [food] in {"Strawberries", "Carrots" ,"Cucumbers"} , "Vegetables ",

    "Desserts"

    )