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.

     

Hans-Georg_Puls's avatar
Hans-Georg_Puls
Icon for Super User rankSuper User
3 months ago

Hi AllanBerces ,

I'm not completely sure what your requirements are but there are definitely a few points in your definition that are probably incorrect:

  1. Part of the first switch entry is 'Table01'[ColumnA] = "0" and it is "or" linked to the first condition. The second part of the condition for your second switch entry is exactly the same. But it will be never relevant, because every 'Table01'[ColumnA] = "0" match will be caught by the first entry. You should think about the second condition of every entry.
  2. DISC is a substring of DISCHARGE. The leads to the same situation for your first condition.  'Table01'[ColumnA], "DISCHARGE HOSE" ) will never be relevant. Every columnA containing a substring "DISC" will be caught by the first condition of the first entry.

Hope that helps already. If not, please provide some more details about what is the expected result and what is the current result.