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 ,

 

Thnaks. But How can I Join these conditions: 

The ones in V0 with the others ?

 

 

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"),"Other",
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(

 

 

tackytechtom's avatar
tackytechtom
Most Valuable Professional
4 years ago

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/

  • cristianml's avatar
    cristianml
    Post Prodigy
    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.