Forum Discussion
Solved
IF EXACT + CONTAINSSTRING (Custom Column)
Hi, I created a custom column and I want to include a variable IF contains EXACT names, else the rest of the formulas. I tried to use EXACT + IN to group those specific names that I want to c...
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 !
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 ")
RETURN
IF(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/