Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
Solved! Go to 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
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.
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
12 | |
11 | |
7 | |
6 |