Forum Discussion
Help with formula please
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!
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✌️!!
5 Replies
- Greg_Deckler
Community Champion
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. 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
Super User
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✌️!!
