Forum Discussion
Row filtering
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!
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)
1 Reply
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)