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 !

     
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.