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] > 0, 2, 1
ELIF(table[col] = "long" & table[col2] > 0, 1, 2
ELSE, 1, 2)
 
i can't figure out how to get this in a simple measure rather than a new column
  • 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

4 Replies

  • 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] )
    )
)
  • v-easonf-msft's avatar
    v-easonf-msft
    Icon for Community Support rankCommunity Support
    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