Forum Discussion

AllanBerces's avatar
AllanBerces
Icon for Post Prodigy rankPost Prodigy
3 months ago
Solved

Switch -Containsstring

Hi good day can anyone correct my calculated column. basically what i required is if the column A contain "DISC" and "SILICON" should be equal to 0, and if contain "DISCHARGE DISC" should be equal to...
Need Help
  • v-veshwara-msft's avatar
    v-veshwara-msft
    3 months ago

    Hi AllanBerces ,

    Thanks for reaching out to the Microsoft Fabric Community.

    The behavior occurs because CONTAINSSTRING() performs partial matching, so "DISC" can also match text within another word such as "DISCHARGE". If the requirement is to match "DISC" as a standalone word only, you can try the following calculated column:

    Subtable = 
VAR _Text = " " & UPPER('Table01'[ColumnA]) & " "
RETURN
SWITCH(
    TRUE(),
    CONTAINSSTRING(_Text, " DISCHARGE DISC "), 1,
    CONTAINSSTRING(_Text, " DISC ")
        || CONTAINSSTRING(_Text, " SILICON "), 0,
    BLANK()
)

     

    I tested this with sample data and got the expected results below:

     

    Please find attached .pbix for reference.

    Hope this helps. Please reach out for further assistance.
    Thank you.

     

v-veshwara-msft's avatar
v-veshwara-msft
Icon for Community Support rankCommunity Support
3 months ago

Hi AllanBerces ,

Thanks for reaching out to the Microsoft Fabric Community.

The behavior occurs because CONTAINSSTRING() performs partial matching, so "DISC" can also match text within another word such as "DISCHARGE". If the requirement is to match "DISC" as a standalone word only, you can try the following calculated column:

Subtable = 
VAR _Text = " " & UPPER('Table01'[ColumnA]) & " "
RETURN
SWITCH(
    TRUE(),
    CONTAINSSTRING(_Text, " DISCHARGE DISC "), 1,
    CONTAINSSTRING(_Text, " DISC ")
        || CONTAINSSTRING(_Text, " SILICON "), 0,
    BLANK()
)

 

I tested this with sample data and got the expected results below:

 

Please find attached .pbix for reference.

Hope this helps. Please reach out for further assistance.
Thank you.