# DAX formula for multiple criteria

Good evening.

I am struggling to solve for comp location based on multiple criteria. The formula in Excel is complex and I was looking to see if there were a better way to solve for the comp location in Power BI. The current formula only solves for AST1, AST2, and AST3, but I need to also solve for AST4, AST5, and AST6, possibly more.

Here is the current Excel formula with illustration for AST1, AST2, and AST3. I do not have an illustration with the additional AST4, AST5, and AST6. I have attached a sample of the data I am working with. The sample is in Excel and the reps have been split using Power Query.
Example Data 12-7-21.xlsx (276.1 KB)

=IF(AND(A12=“BLANK1”,B12=“BLANK2”,C12=“BLANK3”),“Investigate”,IF(AND(A12=B12,A12=C12),A12,IF(AND(A12<>“BLANK1”,B12=“BLANK2”,C12=“BLANK3”),A12,IF(AND(A12=C12,B12=“BLANK2”),A12,IF(AND(A12<>C12,A12<>“BLANK1”,C12<>“BLANK3”,B12=“BLANK2”),“House”,IF(AND(A12<>“BLANK1”,B12<>“BLANK2”,A12<>B12,C12=“BLANK3”),“House”,IF(AND(A12=“BLANK1”,B12<>“BLANK2”,C12=“BLANK3”),B12,IF(AND(A12=“BLANK1”,B12<>“BLANK2”,B12<>C12),“House”,IF(AND(A12=“BLANK1”,B12=“BLANK2”,C12<>“BLANK3”),C12,IF(AND(A12<>“BLANK1”,B12<>“BLANK2”,C12<>“BLANK3”,A12<>B12,B12<>C12),“House”,IF(AND(A12=“BLANK1”,B12=C12),B12,“ERROR”)))))))))))

Welcome to the forum - great to have you here!

Egads, that’s a nasty formula! There are three immediate thoughts I have on how to break that down to more manageable form:

1. Use variables
1. Get rid of all those nested IF statements with a SWITCH(TRUE()) construct
1. SImplify the complex AND conditions using dummy variables

I hope this is helpful. If you still have problems after going through this info and giving it a go, please post your PBIX work-in-progress file and I’d be glad to work through any remaining issues with you.

• Brian
1 Like

Create all the conditions like below

2 Likes

Well, there you go – just like I said.

As usual, beautifully coded @Rajesh!

– Brian

1 Like

@Rajesh Thank you for your reply! It is greatly appreciated. Now if I needed to add AST4, AST5, and AST6, could that be easily done or would that break this formula?

1 Like

1 Like

@BrianJ @Rajesh I am getting "The syntax for ‘)’ is incorrect. Can you take a look at my formula and let me know what I am doing wrong?
Sample Data 12-8-21.pbix (99.6 KB)

@aliciab83

You can easily add AST4, AST5 and AST6.

Condition2 and Condition4, If there are more than 2 conditions inside IF, remove “AND” and use “,” like Condition1. That’s it.

@aliciab83

Here 2 mistakes you are doing

1. Not referring to correct table - It’s “Sales” table not “Table”
2. Not Using “RETURN” statement

To consume Variables in output you’ve to use return statement.

@Rajesh I corrected both mistakes and now have a Result column. However, the results showing are not populating correctly. My 1st row result should shown 009-T but it shows as blank. Is there something else I am missing in the conditions?

Sample Data 12-8-21.pbix (100.7 KB)

@aliciab83

• You’ve to use Blank() function instead of “Blank”. Like below

• First row is not passing any of those conditions.
If there are more conditions, please add all the conditions and check

Can you please explain why it should show 009-T ?

It should show 009-T because AST1 is showing as 009-T and AST2 and AST3 are blank. The same goes for AST1 blank, AST2 009-T and AST3 blank, the result should be 009-T because that is result for AST2 as well as AST1 blank, AST2 blank and AST3 009-T, result should be 009-T because that is the result for AST3.

As I look at this formula, do I need to created more VAR conditions for AST2 and AST3? How would I incorporate AST4, AST5, and AST6, if needed? Sorry for all the questions. I am a Power BI novice looking forward to increasing my knowledge utilizing Enterprise DNA training.

@aliciab83

If condition3 is true you mentioned AST2, but AST1.

Yes, you’ve to add all the conditions

I checked the conditions and was able to match what you are showing. When I added the additional conditions 7 - 11, I end up with a lot of errors.

Here is the formula I am looking to re-write.

=IF(AND(A12=“BLANK1”,B12=“BLANK2”,C12=“BLANK3”),“Investigate”,IF(AND(A12=B12,A12=C12),A12,IF(AND(A12<>“BLANK1”,B12=“BLANK2”,C12=“BLANK3”),A12,IF(AND(A12=C12,B12=“BLANK2”),A12,IF(AND(A12<>C12,A12<>“BLANK1”,C12<>“BLANK3”,B12=“BLANK2”),“House”,IF(AND(A12<>“BLANK1”,B12<>“BLANK2”,A12<>B12,C12=“BLANK3”),“House”,IF(AND(A12=“BLANK1”,B12<>“BLANK2”,C12=“BLANK3”),B12,IF(AND(A12=“BLANK1”,B12<>“BLANK2”,B12<>C12),“House”,IF(AND(A12=“BLANK1”,B12=“BLANK2”,C12<>“BLANK3”),C12,IF(AND(A12<>“BLANK1”,B12<>“BLANK2”,C12<>“BLANK3”,A12<>B12,B12<>C12),“House”,IF(AND(A12=“BLANK1”,B12=C12),B12,“ERROR”)))))))))))

Sample Data 12-8-21.pbix (100.4 KB)

@aliciab83

Fixed the code. Please see how I write the conditions.

How I used “AND” and “&&”
Examples
Condition1 we’ve more that 2 conditions inside if, so I used “&&”
Condition2 We’ve 2 conditions, so I used “AND”
If you want you can remove “AND” and use && like condition1.

First complete some basic courses in DAX. Here we are using only “IF” and “SWITCH” statements.

Finally DAX looks simple, but It’s not easy

Sample Data 12-8-21.pbix (101.4 KB)

2 Likes

Thank you so much @Rajesh ! Your help has been much appreciated. I know I have a lot to learn and recently started Enterprise DNA training. Looking forward to getting the hang of this and having a better understanding of DAX. Again, thank you so much for your time and help.

2 Likes