DAX formula for multiple criteria

[aliciab83]

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”)))))))))))

Thank you in advance.

[BrianJ]

@aliciab83,

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

How To Use Variables in DAX Formula with Power BI

What Does "Variables Are Constants" Really Mean?

2. Get rid of all those nested IF statements with a SWITCH(TRUE()) construct

Dealing With Multiple IF Statements In Power BI Using DAX

3. SImplify the complex AND conditions using dummy variables

Write Smarter DAX With Dummy Variables - Power BI

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

[Rajesh]

Hi @aliciab83

Create all the conditions like below

[BrianJ]

@aliciab83 ,

Well, there you go – just like I said.

As usual, beautifully coded @Rajesh!

– Brian

[aliciab83]

@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?

[aliciab83]

@BrianJ Thank you for your reply as well!

[aliciab83]

@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)

[Rajesh]

@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.

[Rajesh]

@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.

[aliciab83]

@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)

[Rajesh]

@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 ?

[aliciab83]

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.

[Rajesh]

@aliciab83

Please check the conditions carefully.
If condition3 is true you mentioned AST2, but AST1.

Yes, you’ve to add all the conditions

[aliciab83]

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)

[Rajesh]

@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)

[aliciab83]

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.