Forum Discussion
IF Statement help with multiple variables
I have a data set that includes columns for employee names and job titles. I have created an IF statement so that if a job title equals a certain title, it will show with a certain value in my new column. Example of this IF statement: IF([TITLE]="PROJECT EXECUTIVE", "50", IF([TITLE]="Assistant Project Manager", "25")). Now i need help editing this statement so that if the a person's name is "John Doe" whose TITLE is "Project Executive" it will show a value of 65 or if the persons name is "Jane Doe" who is also a "Project Executive" it will show 40, but all others with a Title of "Project Executive" will show a value of 50.
this is what my out put should show:
Not sure how to edit my IF statement to accomplish this. any help is greatly appreciated.
I started with the dataset below. Because I edited the file it reordered the columns.
[Value] is a Calculated Column.
Adding [RateType] is just a suggestion. You can accomplish what you asked (not adding a helper column) by doing:
Value = SWITCH( TRUE(), 'Table'[Name] = "John Doe" && 'Table'[Title] = "Project Executive", 65, 'Table'[Name] = "Jane Doe" && 'Table'[Title] = "Project Executive", 45, 'Table'[Title] = "Project Executive", 50, 25 )
8 Replies
First, use a SWITCH(TRUE()...) statement instead of nested IF statements. Far cleaner, more readable and easier to modify. Second, use the logical operators && (AND) and || (OR) to combine multiple criteria.
thanks for the reply. Rather new to Power BI so trying to learn how to accomplish various tasks. I just replied to another user who suggested somewhat the same as you, but he had the same value for both John and Jane Doe. I need to have it so that John has a value of 65, Jane a value of 40 and all other PE's a value of 50. Not sure how to accomplish this with the info you provided.
thanks for your input.
Edit: Same idea after I re-read your requirement
I would probably just add a helper flag column to your dataset instead of hard-coding Name values.
Value = SWITCH( TRUE(), 'Table'[RateType] = 1 && 'Table'[Title] = "Project Executive", 65, 'Table'[RateType] = 2 && 'Table'[Title] = "Project Executive", 45, 'Table'[Title] = "Project Executive", 50, 25 )Thanks for the reply. I am rather new to Power Bi so trying to learn DAX. can be a bit confusing. Based on your reply, you have the same value for John and Jane Doe. In my original post, Jane Doe has a different value from John and from the other PE's. In my current dataset, my IF statement has multiple nested IF statements due to numerous job titles. This works great, until today when my boss emailed me with changes for just a few users. Previously all users with same job title has same value. now i need John Doe to have one value (65), Jane Doe to have a value of 40 and all other PE's to have a value of 50.
thanks for your help.
Not sure i undestand. it looks like you are creating two new columns (Value and Rate Type). what is the 25 after the 50,? I also have about 15 other job titles that i currently have in my IF statement as well.
is there not a way to have conditions on the If statement to say that if the Title equals PE then 50 unless name equals John Doe, then 65 or if name equals Jane Doe then 40?
have to go into a meeting so will not be able to reply for a bit. thanks again for all your help.
I started with the dataset below. Because I edited the file it reordered the columns.
[Value] is a Calculated Column.
Adding [RateType] is just a suggestion. You can accomplish what you asked (not adding a helper column) by doing:
Value = SWITCH( TRUE(), 'Table'[Name] = "John Doe" && 'Table'[Title] = "Project Executive", 65, 'Table'[Name] = "Jane Doe" && 'Table'[Title] = "Project Executive", 45, 'Table'[Title] = "Project Executive", 50, 25 )
