Forum Discussion

cristianml's avatar
cristianml
Post Prodigy
4 years ago
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...
Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • cristianml's avatar
    cristianml
    4 years ago

    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 !

     
cristianml's avatar
cristianml
Post Prodigy
4 years ago

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.

 

 

 

 

tackytechtom's avatar
tackytechtom
Most Valuable Professional
4 years ago

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/

 

  • cristianml's avatar
    cristianml
    Post Prodigy
    4 years ago

    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.