Determining if event happened in next occurrence

[gavinf4444]

Hello, I have a set of records from various machines which will list any event logs given from an automatic check.  If there are no events to record then this is still saved (shows as blank in the Event column). 

What I want to highlight is if a machine recorded any event (non-blank in Event column) for 2 successive records, eg in my sample data below, I want to show the record for Mac B on 02/01/2023 because it recorded "Non Start" on 01/01/2023 and its subsequent record is "Ignite" on 02/01/2023.

I don't need to highlight Mac C because, although it recorded "Non Start" on 01/01/2023 and "Ignite" on 03/01/2023, it recorded a blank event on 02/01/2023 so effectively its account is reset.

Machine ID	Event	Date	Highlight
Mac A		01/01/2023
Mac B	Non Start	01/01/2023
Mac C	Non Start	01/01/2023
Mac A		02/01/2023
Mac B	Ignite	02/01/2023	x
Mac C		02/01/2023
Mac A		03/01/2023
Mac B		03/01/2023
Mac C	Ignite	03/01/2023

I've seen a few similar requests here involving a calculated column but nothing exactly the same that I can get to work.

[v-jianboli-msft · Accepted solution]

Hi @gavinf4444 ,

Please try:

Highlight = 
var _a = MAXX(FILTER('Table',[Machine ID]=EARLIER('Table'[Machine ID])&&[Event]<>BLANK()&&[Date]<EARLIER('Table'[Date])),[Date])
return IF([Event]<>BLANK()&&[Date]-1=_a,"X")

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.