IF statement for 3 criteria

[JoanCampbell]

Value = IF(AND((Merchandising[Merchandizing Type]="Pack out to shelf" || Merchandising[Merchandizing Type]="Pack out to Exsisting Display"||Merchandising[Merchandizing Type]="Pack out to new display"),(Merchandising[Qty]*(LOOKUPVALUE('Price List'[Unit Price],'Price List'[Item UPC#],Merchandising[UPC from List]))))

I have three criteria Merchandising type= "pack out to shelf", "pack out to existing display" and "pack out to new display"  This is how I wrote the formula, but it gives me an error of having more than 2 arguments.  

Help!

What am I doing wrong? 

[v-stephen-msft]

Hi @JoanCampbell ,

This is a sample of data that I understand, I don't know if it's right.

From the data on the figure, only [Qty] and [Price] of A can be multiplied.

You should copy a category column first, and then pivot table as follows.

Measure

Measure = IF(CALCULATE(DISTINCTCOUNT(Merchandising[Merchandizing Type]),ALLEXCEPT(Merchandising,Merchandising[Category]))=3,MAX('Merchandising'[Qty])*MAX('Merchandising'[Price]))

Calculated column

Column = IF(CALCULATE(DISTINCTCOUNT(Merchandising[Merchandizing Type]),ALLEXCEPT(Merchandising,Merchandising[Category]))=3,[Qty]*[Price])

If my understanding is wrong, provide some sample data and expected results?

Best Regards,

Stephen Tao

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

[TomMartens · Accepted solution]

Hey @JoanCampbell ,

if you are using the DAX and want to use AND then you have to write your DAX like so:

...
IF(
AND( AND( condition1 , condition2 ) , condition3 )
, TRUE branch
, FALSE branch
)
...

using DAX this will work as well, keep in mind that || means OR and && means AND

...
IF(
( condition1 && condition2 && condition3 )
, TRUE branch
, FALSE branch
)
...

Hopefully, this provides the missing link, and helps to tackle your challenge.

Regards,

Tom

[JoanCampbell]

Value = IF((Merchandising[Merchandizing Type]="Pack Out to Shelf"&&Merchandising[Merchandizing Type]="Pack Out to Existing Display"&&Merchandising[Merchandizing Type]="Pack Out to New Display"),(Merchandising[Qty]*Merchandising[Price])) 

comes back blank. 

[TomMartens]

Hey @JoanCampbell ,

you have to use || instead of &&. Using && means AND, the condition asks for a row where the same column has to have 3 different values at the same time.

Please excuse, in my 1st reply to your question I replaced the || by && as I was concentrating more on the combination of three conditions as of the content.

Regards,

Tom