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 consider so I can grup those in "other" but not sure how.

VAR V0 = EXACT('List CG'[Client Group]) IN {"FSO","EVA", "OTHERS"}
 
Also I see i can use :
 
VAR V0 = CONTAINSSTRINGEXACT('List CG'[Client Group], "FSO")

But not sure how to add this to all the specifics words I need to Evaluate. Is possible to use CONTAINSSTRINGEXACT with IN for multiple cases in one variable ?

 

 

Complete formula:

 

CG =
! VAR V0 = CONTAINSSTRINGEXACT('List CG'[Client Group], "FSO") OR CONTAINSSTRINGEXACT('List CG'[Client Group], "EVA")
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(V1,"CMT",
IF(V2,"FS",
IF(V3,"PRD",
IF(V4,"RES",
IF(V5,"H&PS",
IF(V6,"PRD",
IF(V7,"RES",
"Other")))))))
 
 
Here I need to evaluate 19 different words 
 
Hope you can help me.
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 !

     

8 Replies

  • tackytechtom's avatar
    tackytechtom
    Most Valuable Professional
    4 years ago

    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/

    • 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/