Forum Discussion
Continued ContributorNested IF function with an AND
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] )
Community ChampionYour 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
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.- edhans
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.- DemoFour
Hi edhans
Sample data as requested, this is system data put into Excel, it shows null in query editor hence the = null condition.
Hope this gives you what you need, to see if you get a result.Service Request Number Created Date Created Time Status Is VIP Progress Bar Position Send Survey Notification Urgency Closed Date Closed Time Resolved Date Resolved Time Ticket Duration (Days) Last Modifyed Datetime Custom Closed Resolve Diff 252273 13-Sep-22 12:56:14 Closed No 5 No Low 22-Sep-22 12:56:23 13-Sep-22 12:56:21 1 22/09/2022 12:56 1 9 229185 08-Jan-21 13:20:46 Closed No 5 No Low 19-Jan-21 13:20:52 08-Jan-21 13:20:49 1 19/01/2021 13:20 1 11 245846 04-Apr-22 10:19:07 Cancelled No 15 No Low 04-Apr-22 14:57:31 1 04/04/2022 14:57 1 222421 16-Jun-20 08:52:10 Cancelled No 15 No Low 16-Jun-20 09:06:38 1 16/06/2020 09:06 1 221609 13-May-20 10:45:56 Cancelled No 15 No Low 13-May-20 11:53:49 13-May-20 11:52:00 1 13/05/2020 11:53 1 0 230058 02-Feb-21 15:57:14 Closed No 5 No Low 02-Feb-21 15:57:23 02/02/2021 15:57 242174 20-Dec-21 16:42:22 Fulfilled Yes 4 No Low 20-Dec-21 16:45:16 1 20/12/2021 16:44 1 244399 28-Feb-22 08:48:41 Fulfilled No 4 No Low 01-Jul-22 10:14:32 124 01/07/2022 10:14 124 257739 14-Feb-23 13:31:18 Awaiting Move Date No No Low 22/02/2023 07:56 36 258101 28-Feb-23 11:36:48 Awaiting Leave Date No No Low 28/02/2023 11:57 22 236578 02-Aug-21 15:12:27 Submitted No 1 No Low 03/08/2021 06:38 597 257936 21-Feb-23 12:10:41 Awaiting Start Date No No Low 08/03/2023 10:25 29 258048 24-Feb-23 17:08:08 Waiting for Customer No 8 No Low 17/03/2023 15:19 26 258495 10-Mar-23 15:01:14 Active No 3 Yes Low 10/03/2023 15:42 12 258506 10-Mar-23 17:06:02 Waiting for 3rd Party No 8 No Low 17/03/2023 10:11 12 258779 20-Mar-23 14:48:29 Active No 3 No Low 20/03/2023 15:05 2