SWITCH Function with multiple hits for criteria

[cerbertttt]

I recently started using Power BI a month ago. I'm currently trying to use SWITCH to sort through a column of text. The main problem that I am running into is that when a row has multiple words that satisfy the criteria for a case that I've coded for, the output column only contains the first case being satisfied, instead of all of the cases being satisfied.

Here's the code I have so far:

SWITCH Hits Test =
SWITCH(
TRUE(),
CONTAINSSTRING('SWITCH Test'[Raw Data], "cat"), "cat",
CONTAINSSTRING('SWITCH Test'[Raw Data], "dog"), "dog",
CONTAINSSTRING('SWITCH Test'[Raw Data], "fox"), "fox",
"Not Target")   

And here's what my table looks like:

Raw Data	SWITCH HitTest	Ideal Result
dog cat	cat	cat, dog
dog dog	dog	dog
dog fox	dog	dog, fox
cat dog	cat	cat, dog
cat cat	cat	cat
cat fox	cat	cat, fox
test	Not Target	Not Target

The solution that I've come up with is to make separate columns for each SWITCH case, but I was wondering if there was another workaround/potential function I could use before doing this as it seems time intensive for larger SWITCH functions. Any help would be greatly appreciated! 

[AlexisOlson · Accepted solution]

This should work as a calculated column:

Ideal_Result = 
VAR _Concat =
    CONCATENATEX (
        FILTER (
            { "cat", "dog", "fox" },
            CONTAINSSTRING ( 'SWITCH Test'[Raw Data], [Value] )
        ),
        [Value],
        ", "
    )
VAR _Result =
    IF ( ISBLANK ( _Concat ), "Not Target", _Concat )
RETURN
    _Result

[cerbertttt]

Thank you so much! This worked perfectly... I just have one follow up question: let's say I want to classify the text using themes (example: if the string contains "cat" AND "dog", then return "domestic pet"); is this something I could tweak your above solution to inlcude by any chance? Or would I have to make a separate column for it.

[AlexisOlson]

That sounds like pretty distinct logic but you can still use the FILTER ( ..., CONTAINSSTRING( ... ) ) pattern to get a list of matches and then use set logic functions like INTERSECT, UNION, or EXCEPT to do the extra logic.