How to create a colomn to see what is between 2 specific events

[JFAschoon]

Good day Friends.

Is it possible to  view/create a colomn to see what multiple events fall between 2 specific events in a colomn.

i have thousand of rows in a data sheet and need to view the events located between PumpON and PupmOFF and i dont want to see the events between PumpOFF and PumpON. see below the example of data that i have and the expected result.

Or is it possible to create a filter to show only whats between 2 specific texts.

Example:

08/29/2023	13:35:02	d2da	m PumpOFF
08/29/2023	13:35:03	d2da	Zone_HAZARD  Switch____
08/29/2023	13:35:03	d2da	Zone_Warn2   Switch____
08/29/2023	13:35:03	d2da	Zone_ID      Switch____
08/29/2023	13:35:04	d2da	m HazardCleared Warning
08/29/2023	13:35:09	d2da	m PumpON
08/29/2023	13:35:09	d2da	m Hazard WarningCleared
08/29/2023	13:35:10	d2da	m PumpOFF
08/29/2023	13:35:10	d2da	Zone_HAZARD  Switch____
08/29/2023	13:35:17	d2da	Gone
08/29/2023	13:35:19	d2da	Zone_Monitor Switch____
08/29/2023	13:35:23	d2da	Zone_Warn2   Switch____
08/29/2023	13:35:25	d2da	m HazardCleared Warning
08/29/2023	13:35:27	d2da	Zone_ID      Switch____
08/29/2023	13:35:27	d2da	m PumpON
08/29/2023	13:35:33	d2da	Zone_Monitor Switch____
08/29/2023	13:35:37	d2da	Zone_ID      Switch____
08/29/2023	13:35:38	d2da	m PumpOFF
08/29/2023	13:35:38	d2da	Zone_Monitor Switch____
08/29/2023	13:35:39	d2da	Zone_Monitor Switch____
08/29/2023	13:35:42	d2da	m PumpON
08/29/2023	13:36:51	d2da	Zone_Warn1   Switch____
08/29/2023	13:36:51	d2da	Zone_Monitor Switch____
08/29/2023	13:36:51	d2da	Zone_ID      Switch____
08/29/2023	13:40:36	d2da	Zone_HAZARD  Switch____
08/29/2023	13:40:36	d2da	m PumpOFF

Expected result:

08/29/2023	13:35:02	d2da	m PumpOFF
08/29/2023	13:35:09	d2da	m PumpON
08/29/2023	13:35:09	d2da	m Hazard WarningCleared
08/29/2023	13:35:10	d2da	m PumpOFF
08/29/2023	13:35:27	d2da	m PumpON
08/29/2023	13:35:33	d2da	Zone_Monitor Switch____
08/29/2023	13:35:37	d2da	Zone_ID      Switch____
08/29/2023	13:35:38	d2da	m PumpOFF
08/29/2023	13:35:42	d2da	m PumpON
08/29/2023	13:36:51	d2da	Zone_Warn1   Switch____
08/29/2023	13:36:51	d2da	Zone_Monitor Switch____
08/29/2023	13:36:51	d2da	Zone_ID      Switch____
08/29/2023	13:40:36	d2da	Zone_HAZARD  Switch____
08/29/2023	13:40:36	d2da	m PumpOFF
08/29/2023	13:40:50	d2da	m PumpON
08/29/2023	13:40:56	d2da	Zone_HAZARD  Switch____
08/29/2023	13:40:56	d2da	m Hazard WarningCleared
08/29/2023	13:40:56	d2da	m PumpOFF

Thanks in advance for any assistance

[Anonymous · Accepted solution]

Hi @JFAschoon ,

I suggest you to add an index column in Power Query Editor to help calculation.

Measure:

Measure = 
VAR _LASTON = CALCULATE(MAX('Table'[Index]),FILTER(ALL('Table'),'Table'[Column3] = "m PumpON" && 'Table'[Index] <=MAX('Table'[Index])))
VAR _LASTOFF = CALCULATE(max('Table'[Index]),FILTER(ALL('Table'),'Table'[Column3] = "m PumpOFF" && 'Table'[Index] <MAX('Table'[Index])))
RETURN
IF(MAX('Table'[Column3]) in {"m PumpOn","m PumpOFF"} || _LASTON>_LASTOFF,1,0)

Add this measure into the visual level filter and set it to show items when value = 1.

Best Regards,
Rico Zhou

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