Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 🙂
Solved! Go to Solution.
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] )
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingYour 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHay @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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @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 |