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] )

     

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

Your and condition is fine. Are you sure both  [Closed Date] and  [Created Date] have values when that happens? That will cause a null if one or both are null.

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

Hay edhans Thanks for posting. 

 

Yes the other fields have values, the system generates the Closed Date automatically, once the resolved is completed in the process. The user has not undertook this and closed the ticket resulting in a closed date. 

These are outliers in the data set, and the created date is always populated by the system. The missing piece is the resolved date, that I thought I could cover off with the logic. 

It is good that I have got the syntax right, but not so good that I am unsure as to the missing data from the logic.  

Thank you for posting up a response so quickly.