DAX Function to Output Multiple Comma-Separated Values

[Anonymous]

I have an Ice Cream Order Table with one column being Customer Order Description. An example of a data cell from that column is "The customer ordered a small vanilla ice cream with rainbow sprinkles, chocolate chips, and caramel syrup." I have a new column called Toppings Used where I want to list the toppings that were contained in the string of text from Customer Order Description. For example for the previously mentioned order description have "rainbow sprinkles, chocolate chips, caramel syrup" as a data cell with toppings separated by a comma and a space.

Currently I have the DAX function:

Toppings Used = Switch (TRUE(), Containsstring('Ice Cream Order Table'[Customer Order Description], "chocolate chip"), "chocolate chip", Containsstring('Ice Cream Order Table'[Customer Order Description], "fudge"), "fudge sauce", ...)

However, this only gives me one of the toppings. How can I change this code to output multiple values? Thanks in advance.

[Jos_Woolley · Accepted solution]

Hi,

Assuming you have another table named 'Toppings' which comprises a single column (named 'Topping') of all possible toppings, this Calculated Column in the Ice Cream Order Table:

Toppings Used = 
VAR ThisOrder = 'Ice Cream Order Table'[Customer Order Description]
RETURN
    CONCATENATEX(
        FILTER( Toppings, SEARCH( Toppings[Topping], ThisOrder,, 0 ) ),
        Toppings[Topping],
        ", "
    )

Regards

[Anonymous]

This solved my problem, thank you!