Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Gaps in time data

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?

 

Need Help
  • v-xiaotang's avatar
    v-xiaotang
    3 years ago

    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.

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      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.

  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    3 years ago

    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.