Forum Discussion

Dawn85's avatar
Dawn85
Frequent Visitor
5 years ago
Solved

Help with Excel multiple IF formula converted to DAX

Hello,

I have this formula in Excel and I need it rewritten in DAX but I am unsure how to do this. I am very very new to DAX and so far have only been able to do the basics. I hope someone can help!

Excel formula:

Excel Formula:
=IF([@[Date Shipped]]="","",IF([@[Date Exchanged]]="",[@[Date Shipped]]-[@[Date Approved]]+1,[@[Date Shipped]]-[@[Date Exchanged]]+1)-([@[Paused Days (Total)]]*([@[Paused Days (Total)]]<>"")))


My attempt was disastrous and clearly not correct:

Code:
Column = IF(ISBLANK('winnipeg erp_workorder'[Date Shipped]),BLANK(),IF(ISBLANK('winnipeg erp_workorder'[Date Exchanged]),('winnipeg erp_workorder'[Date Shipped]-'winnipeg erp_workorder'[Date Approved])+1),('winnipeg erp_workorder'[Date Shipped]-'winnipeg erp_workorder'[Date Exchanged])+1)-(IF(NOT(ISBLANK('winnipeg erp_workorder'[Paused Days Total],'winnipeg erp_workorder'[Paused Days Total]*'winnipeg erp_workorder'[Paused Days Total]))))


Thank you

  • lbendlin's avatar
    lbendlin
    5 years ago

    Column =
    SWITCH (
        TRUE (),
        ISBLANK ( 'winnipeg erp_workorder'[Date Shipped] ), BLANK (),
        ISBLANK ( 'winnipeg erp_workorder'[Date Exchanged] ),
            'winnipeg erp_workorder'[Date Shipped] - 'winnipeg erp_workorder'[Date Approved] + 1,
        'winnipeg erp_workorder'[Date Shipped] - 'winnipeg erp_workorder'[Date Exchanged] + 1 - 'winnipeg erp_workorder'[Paused Days Total]
    )

4 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    5 years ago

    Column =
    SWITCH (
        TRUE (),
        ISBLANK ( 'winnipeg erp_workorder'[Date Shipped] ), BLANK (),
        ISBLANK ( 'winnipeg erp_workorder'[Date Exchanged] ),
            'winnipeg erp_workorder'[Date Shipped] - 'winnipeg erp_workorder'[Date Approved] + 1,
        'winnipeg erp_workorder'[Date Shipped] - 'winnipeg erp_workorder'[Date Exchanged] + 1 - 'winnipeg erp_workorder'[Paused Days Total]
    )

    • Dawn85's avatar
      Dawn85
      Frequent Visitor
      5 years ago

      Perfect! This is exactly what I was looking for, Thank you very much!

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    5 years ago

    By the way, the formatting is courtesy of www.daxformatter.com - a free service that helps you understand your own DAX code better - I use it all the time.

    • Dawn85's avatar
      Dawn85
      Frequent Visitor
      5 years ago

      I am brand new to DAX so I have a lot to learn, any tool is greatly appreciated, Thank you!