topic Re: DAX Calculate if statement in DAX Commands and Tips

DAX Calculate if statement

jemartin — Mon, 03 Jun 2019 19:27:05 GMT

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

Re: DAX Calculate if statement

parry2k — Mon, 03 Jun 2019 19:56:01 GMT

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.