Assigning role types with IF Function

Anonymous — Tue, 16 Jul 2024 15:11:27 GMT

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

Re: Assigning role types with IF Function

Irwan — Tue, 16 Jul 2024 22:09:54 GMT

hello Anonymous

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.

topic Assigning role types with IF Function in DAX Commands and Tips

Assigning role types with IF Function

Re: Assigning role types with IF Function