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 !
Hi cristianml ,
Here a possible solution:
I would have probably written your code with a switch statement like this:
Column =
SWITCH (
TRUE,
CONTAINSSTRING('List CG'[Client Group],"CMT"), "CMT",
CONTAINSSTRING('List CG'[Client Group],"FS"), "FS",
CONTAINSSTRING('List CG'[Client Group],"PRD"), "PRD",
CONTAINSSTRING('List CG'[Client Group],"RES"), "RES",
CONTAINSSTRING('List CG'[Client Group],"H&PS"), "H&PS",
CONTAINSSTRING('List CG'[Client Group],"PD "), "PD",
CONTAINSSTRING('List CG'[Client Group],"RS "), "RS",
"Other"
)
All the ones that are not listed in the statements will be marked as "others". Also if you would like to do a grouping for another value you could use the or ( || ) operator which essentially does the same as the "in" operator.
Hope this helps a bit 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
HI tackytechtom ,
Thnaks. But How can I Join these conditions:
The ones in V0 with the others ?
Hi cristianml ,
I must admit that I do not really understand what you are trying to achieve. Don't you wanna iterate through the Client Group column to search for a bunch of words and return something in case you get a hit. Plus, all the ones that do not get a hit, shall be marked as "others", right? I think your first solution does all this already, no? Also, why would you wanna join the conditions?
I feel like I am missing something here... 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/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/