IF EXACT + CONTAINSSTRING (Custom Column)

[cristianml]

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.

[tackytechtom]

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]

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]

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 · Accepted solution]

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]

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/

[cristianml]

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]

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]

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.