Calculated column Circular Dependency issue

[skv17]

Hi Guys, I am trying to get the result based of certain conditions to get PASS, FAIL AND MISSING for the DIFOT Status column is a Calculated Column and DOT Status is also a calculated column which gives the correct results

So the DOT Status calc column checks if each rows arrival date time less than arrival cut off, if less than PASS else FAIL , if null then MISSING

for DIFOT Status how do i get a faill for all itemID of a conID as it has failed one? 

So the issue im facing is when i do a count of DIFOT PASS and DIFOT FAIL and MISSING based on conID it gives 1 count to each as DIFOT status has three different values (PASS , FAIL and MISSING)

So if there are 3 conID with 3 items each , all item id of 2 conID has passed all but if last conID has 1 pass 1 fail and 1 missing it gives each of the count to DIFOT PASS and DIFOT FAIL and MISSING , but as the last conID has 1 fail it should fail all it should count as 1 DIFOT FAIL

Please do let me know if you guys have any questions, thanks.

conID	itemID	arrival date time	arrival cut off	DOT Status	DIFOT Status	Required DIFOT Status
ABC	ABC#1	05/06/2024 8:30am	05/06/2024 7:00am	FAIL	FAIL	FAIL
ABC	ABC#2	05/06/2024 6:30am	05/06/2024 7:00am	PASS	PASS	FAIL
ABC	ABC#3	null	05/06/2024 7:00am	MISSING	MISSING	FAIL

[ryan_mayu · Accepted solution]

@skv17 

maybe you can try this

Column =

VAR _DOTCHECK=maxx(FILTER('Table','Table'[conID]=EARLIER('Table'[conID])&&'Table'[DOT Status]="FAIL"),'Table'[conID])

VAR _DIFOTCHECK=maxx(FILTER('Table','Table'[conID]=EARLIER('Table'[conID])&&'Table'[DIFOT Status]="FAIL"),'Table'[conID])

return if(ISBLANK(_DIFOTCHECK)&&ISBLANK(_DOTCHECK),"Pass","FAIL")

[skv17]

this works , thanks you are a legend !!

[ryan_mayu]

you are welcome

[skv17]

Thanks for the reply, will get back to after I have tried this out.