Gaps in time data

[Anonymous]

Hi,

I have a dataset of GPS units with unstable ping frequencies. For one unit there is a row for each ping. if the GPS sends regular pings there will be a ping every 5 minutes, some units have gaps.

I also have a timetable with all the times to create a relationship with data.

I would like to get a status for each time in the timetable if the unit is active, passive, or have no data (no ping).

 Can anybody help with this?

[v-xiaotang · Accepted solution]

Hi @Anonymous 

Thanks for reaching out to us.

>> Is it possible not to make a new table and just a measure

yes, you can try this way

(1) use matrix, then create a measure

Measure = SWITCH(TRUE(),
ISBLANK(MIN('Data table'[dist_since_last_update])),"No data",
MIN('Data table'[dist_since_last_update])=0,"Passive",
MIN('Data table'[dist_since_last_update])>0,"Active"
)

(2) create the measure below, put it into conditional formatting

conditional formatting = SWITCH(TRUE(),
ISBLANK(MIN('Data table'[dist_since_last_update])),"light grey",
MIN('Data table'[dist_since_last_update])>0,"green",
MIN('Data table'[dist_since_last_update])=0,"pink")

Best Regards,

Community Support Team _Tang

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

[amitchandak]

@Anonymous , A new table

addcolumns(generate(Date, time), "Status", Switch(True(),

[Last_update] =[Time] && [Dist_since_last_update] >0 , "Active",

[Last_update] =[Time] && [Dist_since_last_update] =0 , "Passive",

"No Data"

) )

[Anonymous]

Thank you very much! 

Is it possible not to make a new table and just a measure? 

Because I have a lot of units, and dates. If I need to relate that to each time it gives me a huge amount of rows. 

At the moment I have one month of data and it gives me a total of 6.5 million rows.