Forum Discussion

fisqkuz1's avatar
fisqkuz1
Frequent Visitor
4 years ago
Solved

measure with multiple if statements

hi all    coming from a python background, i am trying to get the hang of dax. however I am having trouble with the following attempt:   Revenue or Cost = IF(table[col] = "short" & table[col2...
  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    Hi, fisqkuz1 

    Try measure formula like the following:

     

    Measure = 
var _col=MAX('Table'[col])
var _col2=MAX('Table'[col2])
return 
SWITCH(TRUE(),
    _col="short"&&_col2>0,2,
    _col="short"&&_col2<>0,1,
    _col="long"&&_col2>0,1,
    _col="long"&&_col2<>0,2,
    2
)

     

    If it doesn't work, pelase share your sample data and expected result in excel.

    Best Regards,
    Community Support Team _ Eason

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

Hi fisqkuz1 
I don't fully understand your If - Else statement but you may try the following

Revenue or Cost =
SUMX (
    table,
    IF (
        table[col] = "short" & table[col2] > 0,
        2,
        IF ( table[col] = "long" & table[col2] > 0, 1, 2 )
    )
)
  • fisqkuz1's avatar
    fisqkuz1
    Frequent Visitor
    4 years ago

    hi, thanks for your answer!

    i want to simply get a 1 or 2 as output such as booleans

    where 1 = revenue and 2 = cost

     

    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      4 years ago

      The you use the sane logic. Only last oart shall not be required 

      Revenue or Cost =
SUMX (
    table,
    IF (
        table[col] = "short" & table[col2] > 0,
        table[Revenue],
        IF ( table[col] = "long" & table[col2] > 0, table[Cost] )
    )
)