Forum Discussion
IF EXACT + CONTAINSSTRING (Custom Column)
Hi tackytechtom ,
Don't worry. Finally I solved it 🙂
See below :
CG =VAR V0 = IF(CONTAINSSTRINGEXACT('List CG'[Client Group],"FSO") ||CONTAINSSTRINGEXACT('List CG'[Client Group],"EVA offsets") ||CONTAINSSTRINGEXACT('List CG'[Client Group],"Affiliated Companies") ||CONTAINSSTRINGEXACT('List CG'[Client Group],"Off System NA V&A") ||CONTAINSSTRINGEXACT('List CG'[Client Group],"USA Federal H&PS"),TRUE())VAR V1 = CONTAINSSTRING('List CG'[Client Group],"CMT")VAR V2 = CONTAINSSTRING('List CG'[Client Group],"FS")VAR V3 = CONTAINSSTRING('List CG'[Client Group],"PRD")VAR V4 = CONTAINSSTRING('List CG'[Client Group],"RES")VAR V5 = CONTAINSSTRING('List CG'[Client Group],"H&PS")VAR V6 = CONTAINSSTRING('List CG'[Client Group],"PD ")VAR V7 = CONTAINSSTRING('List CG'[Client Group],"RS ")RETURNIF(V0=TRUE(),"Other",IF(V1,"CMT",IF(V2,"FS",IF(V3,"PRD",IF(V4,"RES",IF(V5,"H&PS",IF(V6,"PRD",IF(V7,"RES","Other"))))))))Thanks !
Awesome!
Just out of curiosity, why do you need the V0? If you remove that line, the result should be the same since it will always go into the "Other" part (your last line of code) if it doesn't find a match.
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
Hi tackytechtom ,
Let me explain:
As CONTAINSSTRING is not case-sensitive in some cases like FS is inside the a word so I need to exclude them Cause actually are not FS:
Case With V0 (correct)
That's why I mentioned at the beginning IF contains EXACT names, else the rest of the formulas.
I hope above description has clarified my situation.
Thnaks and Best regards.
Ahh my bad! 😄
I used the wrong formular when I copy & pasted the code.This is what I actually meant:
Column = SWITCH ( TRUE, CONTAINSSTRINGEXACT('Table8'[Client Group],"CMT"), "CMT", CONTAINSSTRINGEXACT('Table8'[Client Group],"FS"), "FS", CONTAINSSTRINGEXACT('Table8'[Client Group],"PRD"), "PRD", CONTAINSSTRINGEXACT('Table8'[Client Group],"RES"), "RES", CONTAINSSTRINGEXACT('Table8'[Client Group],"H&PS"), "H&PS", CONTAINSSTRINGEXACT('Table8'[Client Group],"PD "), "PD", CONTAINSSTRINGEXACT('Table8'[Client Group],"RS "), "RS", "Other" )And here the result. The last line gets an "other" although it contains an fs (in offset):
Glad you found a solution anyway! 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/Hi tackytechtom ,
Then thing is that using only CONTAINSSTRINGEXACT will be complicated as I have 96 different texts and as this require to be the EXACT text so I would need to write 96 times the funtion. But using CONTAINSSTRING bill be easier cause it indicates whether one string contains another string.
Africa CMT Africa FS Africa H&PS Africa PRD Africa RES ANZ CMT ANZ FS ANZ H&PS ANZ PRD ANZ RES ASGR CMT ASGR FS ASGR H&PS ASGR PRD ASGR RES Brazil CMT Brazil FS Brazil PRD/H&PS Brazil RES Canada CMT Canada FS Canada H&PS Canada PRD Canada RES Gallia CMT Gallia FS Gallia H&PS Gallia PRD Gallia RES Greater China CMT Greater China FS Greater China H&PS Greater China PRD Greater China RES HSA Iberia CMT Iberia FS Iberia H&PS Iberia PRD Iberia RES ICEG CMT ICEG FS ICEG H&PS ICEG PRD ICEG RES India CMT India FS India H&PS India PRD India RES Japan CMT Japan FS Japan H&PS Japan PRD Japan RES ME CMT ME FS ME H&PS ME PRD ME RES Mexico Midwest CMT Midwest FS Midwest H&PS Midwest PRD Midwest RES Nordic CMT Nordic FS Nordic H&PS Nordic PRD Nordic RES Northeast CMT Northeast FS Northeast H&PS Northeast PRD Northeast RES SEA CMT SEA FS SEA H&PS SEA PRD SEA RES South CMT South FS South H&PS South PRD South RES UK, Ireland CMT UK, Ireland FS UK, Ireland H&PS UK, Ireland PRD UK, Ireland RES West CMT West FS West H&PS West PRD West RES Thanks anyway.
Best regrds.