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
FaisalImam
Helper I
Helper I

Need Better DAX Query

Hi Team, 

 Need help with DAX query.

 

We need to report whether an incident is Inside SLA or Outside SLA 

Condition/Criteria - 

If INC  is P1 and time taken to resolve more than 4 hrs - OSLA

If INC  is P2 and time taken to resolve more than 8 hrs - OSLA

If INC  is P3 and time taken to resolve more than 5 days - OSLA

If INC  is P4 and time taken to resolve more than 7 days - OSLA

 

I tried this query (method1) :

 

SLA MET =
SWITCH(
TRUE(),
Incident[PRIORITY] = "1 - Critical" && Incident[Actual elapse time] > 240 , "OUTSIDE SLA" ,
Incident[PRIORITY] = "2 - High" && Incident[Actual elapse time] > 480 , "OUTSIDE SLA" ,
Incident[PRIORITY] = "3 - Moderate" && Incident[Actual elapse time] > 7200 , "OUTSIDE SLA" ,
Incident[PRIORITY] = "4 - Low" && Incident[Actual elapse time] > 10080 , "OUTSIDE SLA" ,
"INSIDE SLA"
)
However , I noticed that it does not satisfy every condition. 
Condition it does not satisfy - What if and INC is still open and already crossed the given time period,it should come in OSLA . (since Actual elapse time is calutated as timedifference between INC opened and resolved). With this solution , all the INC that are still open always comes under ISLA.
 
Another Query (method2) :
SLA MET 2 =
SWITCH(
TRUE(),
Incident[PRIORITY] = "1 - Critical" && Incident[Actual elapse time] = BLANK() && Incident[Ageing Mins] >240, "OUTSIDE SLA" ,
Incident[PRIORITY] = "1 - Critical" && Incident[Actual elapse time] > 240 , "OUTSIDE SLA" ,
Incident[PRIORITY] = "2 - High" && Incident[Actual elapse time] = BLANK() && Incident[Ageing Mins] > 480 , "OUTSIDE SLA" ,
Incident[PRIORITY] = "2 - High" && Incident[Actual elapse time] > 480 , "OUTSIDE SLA" ,
Incident[PRIORITY] = "3 - Moderate" && Incident[Actual elapse time] = BLANK() && Incident[Ageing Mins] > 7200, "OUTSIDE SLA" ,
Incident[PRIORITY] = "3 - Moderate" && Incident[Actual elapse time] > 7200 , "OUTSIDE SLA" ,
Incident[PRIORITY] = "4 - Low" && Incident[Actual elapse time] = BLANK() && Incident[Ageing Days] >10080 , "OUTSIDE SLA" ,
Incident[PRIORITY] = "4 - Low" && Incident[Actual elapse time] > 10080 , "OUTSIDE SLA" ,
"INSIDE SLA"
)
 
This logic seems correct to me and giving better result compared to Query1.
My request is
1.  Whether my logics are correct ?
2. If  Query 2  can be simplified/shortened/made more professional or is there any better logic ? As my above logic seems very noob. 
 
(Just want be become better with Coding ) Thanks in Advance.

 

 

1 ACCEPTED SOLUTION

I was assuming that if the elapsed time is blank amd ageing Mins > 240, irrespective of the priority, the result should be ""OUTSIDE SLA" (which is what the first line of code in the Switch expression checks).

If there are different criteria for each priority where the elapsed time is blank, can you specify them?

Does your second method deliver the expected result? If so, it can also be written like this:

 

SLA MET 2 =
SWITCH (
    TRUE (),
    Incident[PRIORITY] = "1 - Critical"
        && OR (
            Incident[Actual elapse time] > 240,
            AND ( Incident[Actual elapse time] = BLANK (), Incident[Ageing Mins] > 240 )
        ), "OUTSIDE SLA",
    Incident[PRIORITY] = "2 - High"
        && OR (
            Incident[Actual elapse time] > 480,
            AND ( Incident[Actual elapse time] = BLANK (), Incident[Ageing Mins] > 480 )
        ), "OUTSIDE SLA",
    Incident[PRIORITY] = "3 - Moderate"
        && OR (
            Incident[Actual elapse time] > 7200,
            AND ( Incident[Actual elapse time] = BLANK (), Incident[Ageing Mins] > 7200 )
        ), "OUTSIDE SLA",
    Incident[PRIORITY] = "4 - Low"
        && OR (
            Incident[Actual elapse time] > 10080,
            AND ( Incident[Actual elapse time] = BLANK (), Incident[Ageing Days] > 10080 )
        ), "OUTSIDE SLA",
    "INSIDE SLA"
)

 

 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

5 REPLIES 5
PaulDBrown
Community Champion
Community Champion

Try:

SLA MET =
SWITCH (
    TRUE (),
    Incident[Actual elapse time] = BLANK ()
        && Incident[Ageing Mins] > 240, "OUTSIDE SLA",
    Incident[PRIORITY] = "1 - Critical"
        && Incident[Actual elapse time] > 240, "OUTSIDE SLA",
    Incident[PRIORITY] = "2 - High"
        && Incident[Actual elapse time] > 480, "OUTSIDE SLA",
    Incident[PRIORITY] = "3 - Moderate"
        && Incident[Actual elapse time] > 7200, "OUTSIDE SLA",
    Incident[PRIORITY] = "4 - Low"
        && Incident[Actual elapse time] > 10080, "OUTSIDE SLA",
    "INSIDE SLA"
)




Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Hi ,

I did not get this part :

Incident[Actual elapse time] = BLANK () && Incident[Ageing Mins] > 240, "OUTSIDE SLA",

 

Ageins mins criteria is different for different Priority. Above query you are just checking for P1.

 

 

 

I was assuming that if the elapsed time is blank amd ageing Mins > 240, irrespective of the priority, the result should be ""OUTSIDE SLA" (which is what the first line of code in the Switch expression checks).

If there are different criteria for each priority where the elapsed time is blank, can you specify them?

Does your second method deliver the expected result? If so, it can also be written like this:

 

SLA MET 2 =
SWITCH (
    TRUE (),
    Incident[PRIORITY] = "1 - Critical"
        && OR (
            Incident[Actual elapse time] > 240,
            AND ( Incident[Actual elapse time] = BLANK (), Incident[Ageing Mins] > 240 )
        ), "OUTSIDE SLA",
    Incident[PRIORITY] = "2 - High"
        && OR (
            Incident[Actual elapse time] > 480,
            AND ( Incident[Actual elapse time] = BLANK (), Incident[Ageing Mins] > 480 )
        ), "OUTSIDE SLA",
    Incident[PRIORITY] = "3 - Moderate"
        && OR (
            Incident[Actual elapse time] > 7200,
            AND ( Incident[Actual elapse time] = BLANK (), Incident[Ageing Mins] > 7200 )
        ), "OUTSIDE SLA",
    Incident[PRIORITY] = "4 - Low"
        && OR (
            Incident[Actual elapse time] > 10080,
            AND ( Incident[Actual elapse time] = BLANK (), Incident[Ageing Days] > 10080 )
        ), "OUTSIDE SLA",
    "INSIDE SLA"
)

 

 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Thanks, this works perfectly. Thought its more complex than what I wrote but  helped me in understanding how we can use logical operators.

Bdw, any problem/complexity issue with the query I wrote or it can be used as well ?

I don't think there should be any problems with how you wrote the query. Reducing the SWITCH expression from your 9 lines to the more succinct 5 lines might help improve performance though...





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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.