Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
cerbertttt
New Member

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 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 DataSWITCH HitTest Ideal Result
dog catcatcat, dog
dog dogdogdog
dog foxdogdog, fox
cat dogcatcat, dog
cat catcatcat
cat foxcatcat, fox
testNot TargetNot 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! 

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

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

View solution in original post

3 REPLIES 3
AlexisOlson
Super User
Super User

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.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.