Row filtering

[Anonymous]

Hi,

Data

I have a dataset in which different units (unit_id's) get an update every 10 minutes. There are multiple columns under which a datetime column, a idle_time column and unit_id column. 

Necessary

I need just the rows in which the next value of idle_time is 0 (per unit).

So for example from unit 1&2:

update_id	unit_id	datetime	idle_time
1	1	1-5-2021 10:10	53
2	1	1-5-2021 10:20	63
3	2	1-5-2021 10:23	12
4	1	1-5-2021 10:30	0
5	2	1-5-2021 10:33	22
6	2	1-5-2021 10:43	0

I now just need the bold rows (the peeks per unit). All updates and al units are in the same table. How can i filter the rows out for every unit, where the next idle_time value is 0? So it's based on the date-time increasing (which is easy to ascend or descend).

A unit has multiple updates a day, and also multiple peaks. That's what makes it difficult. 

Thanks!

[HotChilli · Accepted solution]

I haven't tested this at all so see how you get on (with multiple 0 values for same id etc).

It's a column

NextOneIsZero = VAR _unit = TableM[unit_id]
VAR _update = TableM[update_id]
VAR _0ID = CALCULATE(MIN(TableM[update_id]), FILTER(TableM, TableM[unit_id] = _unit && TableM[idle_time] = 0 && TableM[update_id] > _update))
VAR _prev0ID = CALCULATE(MAX(TableM[update_id]), FILTER(TableM, TableM[unit_id] = _unit && TableM[update_id] < _0ID))
RETURN
IF (_prev0ID = _update, 1,0)