Assigning role types with IF Function

[CiaraCaryl]

Maybe there is a better way to do this but I am basically trying to create a column where if the employees title

• Contains "Account Executive" but not "Enterprise" it returns "AE",
• If it contains both "Account Executive" and "Enterprise" it returns "EAE"
• If it contains neither it returns "Other"

The formula I am trying to use is: 

Role Type = (IF(NOT(CONTAINSSTRING(Opportunity[AE TItle], "Enterprise")

&& CONTAINSSTRING(Opportunity[AE TItle], "Account Executive")), "AE")

&& IF(CONTAINSSTRING(Opportunity[AE TItle], "Enterprise"), "EAE")

&& IF(NOT(CONTAINSSTRING(Opportunity[AE TItle], "Account Executive")), "Other"))

Is there a better formula I could use or is my syntax wrong?

Thank you

[Irwan · Accepted solution]

hello @CiaraCaryl 

your syntax looks good but here are some notes you probably want to put a note.

- in Line 4, NOT function is use for both containsstring but you need opposite value only for "Enterprise" for "AE".

- use comma in-between if statement

Here is one way to represent if statement based on your DAX.

Role Type =

IF(

    CONTAINSSTRING('Opportunity'[AE Title],"Account Executive")&&NOT CONTAINSSTRING('Opportunity'[AE Title],"Enterprise"),

    "AE",

IF(

    CONTAINSSTRING('Opportunity'[AE Title],"Enterprise"),

    "EAE",

IF(

    NOT CONTAINSSTRING('Opportunity'[AE Title],"Account Executive"),

    "Other"

)))

Another way to write is using SWITCH.

Role Type Switch =

SWITCH(

    TRUE(),

    CONTAINSSTRING('Opportunity'[AE Title],"Account Executive")&&NOT CONTAINSSTRING('Opportunity'[AE Title],"Enterprise"),"AE",

    CONTAINSSTRING('Opportunity'[AE Title],"Enterprise"),"EAE",

    NOT CONTAINSSTRING('Opportunity'[AE Title],"Account Executive"),"Other"

)

Hope this will help you.

Thank you.