Forum Discussion

jemartin's avatar
jemartin
New Member
7 years ago
Solved

DAX Calculate if statement

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's avatar
    parry2k
    7 years ago

    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.

     

     

1 Reply

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    7 years ago

    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.