Forum Discussion
Need Better DAX Query
- 3 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" )
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"
)
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 ?
- PaulDBrown3 years agoCommunity Champion
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...