Forum Discussion
Solved
SWITCH Function with multiple hits for criteria
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 ...
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
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
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.
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.