Forum Discussion
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.
Custom Column =
if [Status] = "Cancelled"
then Duration.Days( [Closed Date] - [Created Date] ) +1
else
if [Status] = "Closed"
then
Duration.Days( [Resolved Date] - [Created Date] ) +1
else
if [Status] = "Closed" and [Resolved Date] = null
then Duration.Days( [Closed 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] )
It is the 3rd statement, that I need to review, I need there to have a condition where some closed tickets have not got a resolve date. So I have added an AND statement to the condition.
Not really and M expert, so what have i done wrong, as this condition produces no output.
Thanks in advance 🙂
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] )
6 Replies
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] )Yes, once any of the conditions is matched, all further conditions are ignored. Glad you got it working.
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.
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.Can you post some sample data?
How to get good help fast. Help us help you.
How To Ask A Technical Question If you Really Want An Answer
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.
