Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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.  ...
Need Help
Show and Tell
  • HotChilli's avatar
    HotChilli
    5 years ago

    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)

     

HotChilli's avatar
HotChilli
Community Champion
5 years ago

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)