Forum Discussion

DemoFour's avatar
DemoFour
Icon for Continued Contributor rankContinued Contributor
3 years ago
Solved

Nested IF function with an AND

Hay peeps,  Can anyone tell me what I have done wrong, I am building this in the query editor and not DAX, as I can't do it in the source system but want to push it back to my ETL dataflow.    C...
Need Help
  • DemoFour's avatar
    DemoFour
    3 years ago

    Hi edhans 

    I have sorted it! 

    I swapped the order of the AND condition to be before the single closed condition and it now gives a result, i guess the logic stopped the other way round and did not progress to the AND condition.  

     

    if [Status] = "Cancelled" 
           then Duration.Days( [Closed Date] - [Created Date]  ) +1
            else 
                if [Status] = "Closed" and [Resolved Date] = null 
                then Duration.Days( [Closed Date] - [Created Date] ) +1
                   else
                    if [Status] = "Closed"
                        then Duration.Days( [Resolved Date] - [Created Date] ) +1
                            else 
                                if [Status] = "Fulfilled" 
                                    then Duration.Days( [Resolved Date] - [Created Date] ) +1
                                        else 
                                        Duration.Days( Date.From(DateTime.FixedLocalNow ()) - [Created Date] )

     

DemoFour's avatar
DemoFour
Icon for Continued Contributor rankContinued Contributor
3 years ago

Hi edhans 

I have sorted it! 

I swapped the order of the AND condition to be before the single closed condition and it now gives a result, i guess the logic stopped the other way round and did not progress to the AND condition.  

 

if [Status] = "Cancelled" 
           then Duration.Days( [Closed Date] - [Created Date]  ) +1
            else 
                if [Status] = "Closed" and [Resolved Date] = null 
                then Duration.Days( [Closed Date] - [Created Date] ) +1
                   else
                    if [Status] = "Closed"
                        then Duration.Days( [Resolved Date] - [Created Date] ) +1
                            else 
                                if [Status] = "Fulfilled" 
                                    then Duration.Days( [Resolved Date] - [Created Date] ) +1
                                        else 
                                        Duration.Days( Date.From(DateTime.FixedLocalNow ()) - [Created Date] )

 

  • edhans's avatar
    edhans
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Yes, once any of the conditions is matched, all further conditions are ignored. Glad you got it working.