My understanding is that the formula would evaluate each condition to check if the result was “TRUE” and then return a “Fail” if any of the conditions resulted in “FALSE”.
The below is what is supposed to happen:
(1) SLA E2HR ORIGINAL =
SWITCH (
TRUE (),
AND (
SELECTEDVALUE ( ‘MTSE (Completed)’[Maintenance Type] ) = “Emergency 2 Hr”, //Check to see if it Maintenance Type is Emergency 2 HR
SELECTEDVALUE ( ‘MTSE (Completed)’[Is Working Day] ) = “Workday” //Check to see if it is a Working and not a Non Working Day
), “Pass”,
AND (
[Completed Date] <= [Due Date], //Check to see if the works are completed before the Due Date
[Completed Time] <= [Emergency 2HR Time Target] //Check to see if the works are completed before the Due Timeframe
), “Pass”,
AND (
[Admin Date] <= [Next Working Day], //Check to see if the works have been updated on the system before the next working day
[Admin Time] <= [Target Time] //Check to see if the works have been updated on the system before the next working day Target Time
), “Pass”,
"Fail" //If non of the above criteria match then this Fails the SLA
)
This Formula is providing mixed results and im not sure where or why its going wrong, ive attached some screenshots as to the results of what is happening with the formula.
And you will see that it is “Passing” on Weekends and Passing when the Maintenance Type is not Emergency 2HR.
Any help, guidance would be greatly appreciated.
PS - pretty new to PBI & DAX so any help, guidance would be greatly appreciated.
The SWITCH function will stop evaluating conditions the first time it finds a match, so in your case, if the first “AND” is true, then it will pass; if one of the 3 “ANDs” is true in all cases, then you’ll never get to the “Fail”. (Hard to confirm without a PBIX, and no time to make-up a sample, but this is the way “cases” are evaluated in Microsoft SQL Server, and AFAIK DAX SWITCH works the same way.)
If all 3 of your conditions must be TRUE in order to be a “Pass”, then wrap all 3 of them in another “AND” (something like, just threw this into the DAX Clean up tool)
( 1 ) SLA E2HR ORIGINAL =
SWITCH(
TRUE(),
AND(
AND(
SELECTEDVALUE( ‘ MTSE( Completed ) ’ [Maintenance Type] ) = “ Emergency 2 Hr ”,
//Check to see IF it Maintenance Type is Emergency 2 HR
SELECTEDVALUE( ‘ MTSE( Completed ) ’ [Is Working Day] ) = “ Workday ” //Check to see IF it is a Working AND NOT a Non Working DAY
),
AND(
[Completed Date] <= [Due Date],
//Check to see IF the works are completed before the Due DATE
[Completed Time] <= [Emergency 2HR TIME Target] //Check to see IF the works are completed before the Due Timeframe
),
AND(
[Admin Date] <= [Next Working Day],
//Check to see IF the works have been updated on the system before the next working DAY
[Admin Time] <= [Target Time] //Check to see IF the works have been updated on the system before the next working DAY Target TIME
),
),
“ Pass ”,
"Fail" //If non of the above criteria match then this Fails the SLA
)
Too many arguments were passed to the AND function. The maximum argument count for the function is 2.
(1) SLA E2HR v5 =
SWITCH (
TRUE (),
AND (
AND(
SELECTEDVALUE ( 'MTSE (Completed)'[Maintenance Type] ) = "Emergency 2 Hr", //Check to see if it Maintenance Type is Emergency 2 HR
SELECTEDVALUE ( 'MTSE (Completed)'[Is Working Day] ) = "Workday" //Check to see if it is a Working and not a Non Working Day
),
AND (
[Completed Date] <= [Due Date], //Check to see if the works are completed before the Due Date
[Completed Time] <= [Emergency 2HR Time Target] //Check to see if the works are completed before the Due Timeframe
),
AND (
[Admin Date] <= [Next Working Day], //Check to see if the works have been updated on the system before the next working day
[Admin Time] <= [Target Time] //Check to see if the works have been updated on the system before the next working day Target Time
),
),
"Pass",
"Fail" //If non of the above criteria match then this Fails the SLA
)
It’s hard to replicate same without sample file but I suggest you use && instead of AND function. Replicated Greg’s code with &&
(1) SLA E2HR v5 =
SWITCH (
TRUE (),
( SELECTEDVALUE ( 'MTSE (Completed)'[Maintenance Type] ) = "Emergency 2 Hr"
&& //Check to see if it Maintenance Type is Emergency 2 HR
SELECTEDVALUE ( 'MTSE (Completed)'[Is Working Day] ) = "Workday" //Check to see if it is a Working and not a Non Working Day
)
&&
(
[Completed Date] <= [Due Date]
&& //Check to see if the works are completed before the Due Date
[Completed Time] <= [Emergency 2HR Time Target] //Check to see if the works are completed before the Due Timeframe
)
&&
(
[Admin Date] <= [Next Working Day]
&& //Check to see if the works have been updated on the system before the next working day
[Admin Time] <= [Target Time] //Check to see if the works have been updated on the system before the next working day Target Time
),
"Pass",
"Fail" //If non of the above criteria match then this Fails the SLA
)
Hi @martynross85, did the response provided by the experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!
It looks like the references to the [Is Working Day] column may not be working because the column has a different name (a “one-s” in the DAX formula in post 9 and an “eye-s” is used in post 12); perhaps that’s the issue and it will disappear once you confirm spelling.
The forum members are taking experienced guesses but could really benefit from both a PBIX and a mock-up of your desired outcome if you’d like to take this further. Can you please provide?
As @Greg mentioned I tried to replicate exactly what you have shown in the snapshot but there might be chances that you have a blank and which is hard to figure out only by referring snapshot.Also I must admit that it was really hard to manually type each data and create a sample for this.
What you can do is that you go to your header and copy “Is Working day” and replace in the formula that way it will work same applies to others also if you get any error. Note the above formula must be calculated column not measure.
Thanks for looking into this for me - much appreciated. After much frustration of this failing for me - i decided to go back to the drawing board (As the errors being received must have been a result of something that i have done - without knowing)
I have rebuilt the report from scratch and the only difference i can tell is that i have created the calculated columns in the “Report Area” rather than the “Data Area”.
I didn’t think this would have any impact whatsoever - however it seems to have done the job.
Also much appreciation for the below formula - which now gives us the desired results so thanks guys.
i had to add a MAXX at the start in-order for it to run for me
SLA (1) 2 HR Emergency (Working Hours) =
MAXX(MTSE,
IF (
SELECTEDVALUE(MTSE[Maintenance Type])= "Emergency 2 Hr"
&& SELECTEDVALUE ( MTSE[Is Working Day] ) = "Working Day"
&& SELECTEDVALUE(MTSE[Office Hours]) = "Working Hours"
&& MTSE[Completed Date - Date] <= MTSE[Due Date - Date]
&& MTSE[Completed Date - Time] <= MTSE[Emergency 2HR Target]
&& MTSE[Admin Date - Date] <= MTSE[NextWorkingDay]
&& MTSE[Admin Date - Time] <= MTSE[Target Time ] ,
"Pass",
"Fail"
))
So MASSIVE thanks from me to everyone who helped with this.
Aappreciate a PBIX file would have helped, however it is data from a client so i have had to anonymize data / reference points where possible - hope you understand.
Hi @martynross85, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!
