DAX Calculate if statement

[jemartin]

Hi,

I am pretty new with DAX and struggling to translate this excel formula into DAX format.

I want to calculate the Value based on the criteria;

Sales price * qty   (where Sales price >0)

Budget price * qty  (if Sales price = 0)

Material cost *30% * qty  (if Sales price =0 and Budget price = 0)

In excel it looks like this:

=IF(H119>0;H119*L119;IF(I119>0;I119*L119;(G119*1,3)*L119))

[parry2k · Accepted solution]

@jemartin add new column using following expression:

HVIS = 
SWITCH( 
TRUE(),
Table[Sales Price] > 0, Table[Sales Price] * Table[Qty],
Table[Sales Price] = 0 && Table[Budget Price] = 0,  Table[Material Cost] * 0.3 * Table[Qty],
Table[Sales Price] = 0, Table[Budget Price] * Table[Qty]
)

change column name and table name as per your data model. You can also achieve it with IF but Switch is easy to change/read.