Using switch to find a test inside a text and give a category

[Anonymous]

Hi Gurus, 

I created  below formule, it alwasy return "YES" regardless. "Closed Order is a calculated column inside a tabel.

my 'SAP_WO'[PM_ORDER_SYSTEM_STATUS] columns data is like below

CLSD,NCMP,NMAT,PRC,SETC    Returns "Yes"  (correct result)

REL,ESTC,MACM,PRC,SETC         Returns "Yes" (wrong result, should be "No"

Can you please help me out.

"Closed Orders", SWITCH(

TRUE()

,ISERROR(SEARCH("CNF",'SAP_WO'[PM_ORDER_SYSTEM_STATUS])) , "Yes"

,ISERROR(SEARCH("TECO",'SAP_WO'[PM_ORDER_SYSTEM_STATUS])) , "Yes"

,ISERROR(SEARCH("CLSD",'SAP_WO'[PM_ORDER_SYSTEM_STATUS])) , "Yes"

,"No"

),

[jdbuchanan71]

You have to think about what the switch is doing.  It is finding the first TRUE() statement and returning the string.
ISERROR(SEARCH("CNF",'SAP_WO'[PM_ORDER_SYSTEM_STATUS])) , "Yes" is TRUE on every line that does not have "CNF" so it returns "YES".  

What you want is this

Closed Orders = 
SWITCH (
    TRUE (),
    NOT ISERROR ( SEARCH ( "CNF", 'SAP_WO'[PM_ORDER_SYSTEM_STATUS] ) ), "Yes",
    NOT ISERROR ( SEARCH ( "TECO", 'SAP_WO'[PM_ORDER_SYSTEM_STATUS] ) ), "Yes",
    NOT ISERROR ( SEARCH ( "CLSD", 'SAP_WO'[PM_ORDER_SYSTEM_STATUS] ) ), "Yes",
    "No"
)

NOT ISERROR means it found the text in the string.

[Anonymous]

@jdbuchanan71 , Thanks a lot for the explanation. 

[Anonymous]

Hi, 

Manged to get the desired result with CONTAINSTRING.

"Closed Orders", SWITCH(

TRUE()

,CONTAINSSTRING(SAP_WO[PM_ORDER_SYSTEM_STATUS],"CNF") , "Yes"

,CONTAINSSTRING(SAP_WO[PM_ORDER_SYSTEM_STATUS],"TECO") , "Yes"

,CONTAINSSTRING(SAP_WO[PM_ORDER_SYSTEM_STATUS],"CLSD") , "Yes"

,"No"

),