Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DemoFour
Responsive Resident
Responsive Resident

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

 

 

Resolve date.jpg

 

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 🙂 

1 ACCEPTED SOLUTION
DemoFour
Responsive Resident
Responsive Resident

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

 

View solution in original post

6 REPLIES 6
DemoFour
Responsive Resident
Responsive Resident

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
edhans
Super User
Super User

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
DemoFour
Responsive Resident
Responsive Resident

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
DemoFour
Responsive Resident
Responsive Resident

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 NumberCreated DateCreated TimeStatusIs VIPProgress Bar PositionSend Survey NotificationUrgencyClosed DateClosed TimeResolved DateResolved TimeTicket Duration (Days)Last Modifyed DatetimeCustomClosed Resolve Diff
25227313-Sep-2212:56:14ClosedNo5NoLow22-Sep-2212:56:2313-Sep-2212:56:21122/09/2022 12:5619
22918508-Jan-2113:20:46ClosedNo5NoLow19-Jan-2113:20:5208-Jan-2113:20:49119/01/2021 13:20111
24584604-Apr-2210:19:07CancelledNo15NoLow04-Apr-2214:57:31  104/04/2022 14:571 
22242116-Jun-2008:52:10CancelledNo15NoLow16-Jun-2009:06:38  116/06/2020 09:061 
22160913-May-2010:45:56CancelledNo15NoLow13-May-2011:53:4913-May-2011:52:00113/05/2020 11:5310
23005802-Feb-2115:57:14ClosedNo5NoLow02-Feb-2115:57:23   02/02/2021 15:57  
24217420-Dec-2116:42:22FulfilledYes4NoLow  20-Dec-2116:45:16120/12/2021 16:441 
24439928-Feb-2208:48:41FulfilledNo4NoLow  01-Jul-2210:14:3212401/07/2022 10:14124 
25773914-Feb-2313:31:18Awaiting Move DateNo NoLow     22/02/2023 07:5636 
25810128-Feb-2311:36:48Awaiting Leave DateNo NoLow     28/02/2023 11:5722 
23657802-Aug-2115:12:27SubmittedNo1NoLow     03/08/2021 06:38597 
25793621-Feb-2312:10:41Awaiting Start DateNo NoLow     08/03/2023 10:2529 
25804824-Feb-2317:08:08Waiting for CustomerNo8NoLow     17/03/2023 15:1926 
25849510-Mar-2315:01:14ActiveNo3YesLow     10/03/2023 15:4212 
25850610-Mar-2317:06:02Waiting for 3rd PartyNo8NoLow     17/03/2023 10:1112 
25877920-Mar-2314:48:29ActiveNo3NoLow     20/03/2023 15:052 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors