Forum Discussion

FaisalImam's avatar
FaisalImam
Helper I
4 years ago
Solved

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 I...
Need Help
  • PaulDBrown's avatar
    PaulDBrown
    4 years ago

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

     

     

     

PaulDBrown's avatar
PaulDBrown
Community Champion
4 years ago

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"
)
  • FaisalImam's avatar
    FaisalImam
    Helper I
    4 years ago

    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.

     

     

     

    • PaulDBrown's avatar
      PaulDBrown
      Community Champion
      4 years ago

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

       

       

       

      • FaisalImam's avatar
        FaisalImam
        Helper I
        4 years ago

        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 ?