Switch -Containsstring

[AllanBerces]

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 1

Subtable =
SWITCH (
TRUE (),
CONTAINSSTRING ( 'Table01'[ColumnA], "DISC" ) || 'Table01'[ColumnA] = "0", "0",
CONTAINSSTRING ( 'Table01'[ColumnA], "SILICON" ) || 'Table01'[ColumnA] = "0", "0",
CONTAINSSTRING ( 'Table01'[ColumnA], "DISCHARGE HOSE" ) || 'Table01'[ColumnA] = "1", "1"
)

Thnak you

[Kedar_Pande]

 Subtable =
SWITCH(
TRUE(),
CONTAINSSTRING('Table01'[ColumnA], "DISCHARGE HOSE"), "1",
CONTAINSSTRING('Table01'[ColumnA], "DISC"), "0",
CONTAINSSTRING('Table01'[ColumnA], "SILICON"), "0"
)

SWITCH evaluates top to bottom and stops at the first match. Order matters here.

@AllanBerces

[danextian]

I'm confused. What is the purpose of Table01'[ColumnA] = "0" which is not in your explanation? Also should it contain either DISC and SILICON or DISC or SILICON as the first and second conditions in your DAX point to an OR logic?

[Hans-Georg_Puls]

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.

[v-veshwara-msft · Accepted solution]

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.

[AllanBerces]

Hi @v-veshwara-msft @Kedar_Pande @danextian @Hans-Georg_Puls thank you very much or highligthing and explanation. Work perfectly