Forum Discussion
Assigning role types with IF Function
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:
Is there a better formula I could use or is my syntax wrong?
Thank you
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.
1 Reply
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.