Help with formula please

[Anonymous]

Can you please tell me why my formula does not work?

AE role= if ( [Job Family]="Direct Sales" && (CONTAINS ( Roster,[Job Title],"Account Executive")=TRUE),"AE","")

Many thanks!

[Anonymous]

Sorry, I should have been more clear. I have a roster of people. I want to identfy "Account executives", but I need to use 2 fields for this: I need the job family column to be "Direct Sales" and I need the job title to contain "account executive". I want a new column called "AE role" that says "AE" when both these criteria are true.

Here is my data:

ID	Job Title	Job Family	AE Role
3	Account Executive 4, Business Development - Sales	Business Development - Sales
4	Account Executive 4, Channel Sales	Channel Sales
9	Account Executive 1, Channel Sales	Channel Sales
14	Account Executive 5, Direct Sales	Direct Sales	AE
24	Account Executive 4, Direct Sales	Direct Sales	AE
25	Account Executive 2, Services Sales	Services Sales
26	Account Executive 2, Business Development - Sales	Business Development - Sales

I get this when I try to add a custom column, unless, is there a way to add a conditional column with an AND?

Thank you!!

[Greg_Deckler]

@Anonymous You are trying to use DAX in Power Query and that is not going to work out very well for you. You need to do this in a calculated column in Power BI Desktop, not Power Query Editor. Or you need the M equivalent which is something along the lines of:

if [Job Family] = "Direct Sales" and Text.Contains([Job Title],"Account Executive")then "AE" else null)

[VahidDM · Accepted solution]

Hi @Anonymous 

The @Greg_Deckler solution will 100% cover your request; Just remove that Bracket ) at the end.

and if you want to replace "null" with blank use this:

if [Job Family] = "Direct Sales" and Text.Contains([Job Title] , "Account Executive") then "AE" else ""

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos✌️!!

[Greg_Deckler]

@VahidDM Thanks, missed that paren!!

[Greg_Deckler]

@Anonymous Not really enough information but try:

AE role= if ( [Job Family]="Direct Sales" && (SEARCH("Account Executive",[Job Title],0) > 0 ) ,"AE", "")

Otherwise, Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.