Forum Discussion

tomperro's avatar
tomperro
Icon for Helper V rankHelper V
2 years ago

How to get value based on 2 filters

I am trying to get the latest test number and date of the latest Non-Compliant (NC) test where there has not been another Test of the same test number completed after that NC test. I create 3 calculated columns to store this information.

Last_NC_Test_Number - last NC test number
Last NC DATE - date of the last NC test
Has Outstanding NC - flag if a follow-up test is required if the follow up has been completed with past 30 days.
 
Examples:
An employee had a NC test 123 on 01/01/2023 and has not had another test 123 since 01/01/2023.
- Last_NC_Test_Number = 123
- Last NC DATE - 01/01/2023
- Has Outstanding NC - Y
 
An employee had a NC test 123 on 01/01/2023 and has had another test 123 on 12/01/2023.
- Last_NC_Test_Number = 123
- Last NC DATE - 01/01/2023
- Has Outstanding NC - N
 
An employee had a NC test 345 and NC 123 both on 01/01/2023 and has had another test 345 on 02/01/2023.
- Last_NC_Test_Number_Contact_Column = 123
- Last NC DATE - 01/01/2023
- Has Outstanding NC - Y
 
I have the following formula but it is not retruning what I need because of MAX in calculate:
VAR LastNC_Date = CALCULATE(max(Evaluation[Date__c]),Evaluation[EmployeeId__c] &&  Evaluation[OTI_ResultForFiltering] = "NC" &&  Evaluation[TestNumber__c] <> "NC")
RETURN
CALCULATE(max(Evaluation[TestNumber__c]),  Evaluation[OTI_ResultForFiltering] = "NC" , Evaluation[Date__c] = LastNC_Date)
 
 
Need Help

1 Reply