Forum Discussion
Tracking dashboard
Hi Guys,
I want to create a dashboard showing the rides of assets. I have a table listing tracking events like start, moving, stop. So a typical Ride for one device has a start, one or more moving records and a stop record.
| Time | DeviceID | eventType | lat | lon | msgCounter |
| 12:00 | 1 | start | 0 | 0 | 1 |
| 12:05 | 1 | moving | 1 | 1 | 2 |
| 12:10 | 1 | moving | 1 | 2 | 3 |
| 12:15 | 1 | stop | 1 | 3 | 4 |
Now the trouble is that I have one table for multiple assets. like;
| Time | DeviceID | eventType | lat | lon | msgCounter |
| 12:00 | 1 | start | 0 | 0 | 1 |
| 12:02 | 2 | moving | 1 | 1 | 2 |
| 12:10 | 1 | moving | 1 | 2 | 2 |
| 12:15 | 4 | start | 1 | 3 | 4 |
| 12:15 | 1 | stop | 1 | 3 | 3 |
| 12:16 | 2 | stop | 2 | 2 | 3 |
| 12:17 | 4 | moving | 2 | 2 | 5 |
| 12:20 | 4 | stop | 2 | 3 | 6 |
To be able to show rides on a dashboard I need to create a unique RideID for every ride of a given device
| Time | DeviceID | eventType | lat | lon | msgCounter | RideID |
| 12:00 | 1 | start | 0 | 0 | 1 | 1 |
| 12:02 | 2 | moving | 1 | 1 | 2 | 2 |
| 12:10 | 1 | moving | 1 | 2 | 2 | 1 |
| 12:15 | 4 | start | 1 | 3 | 4 | 1 |
| 12:15 | 1 | stop | 1 | 3 | 3 | 1 |
| 12:16 | 2 | stop | 2 | 2 | 3 | 2 |
| 12:17 | 4 | moving | 2 | 2 | 5 | 1 |
| 12:20 | 4 | stop | 2 | 3 | 6 | 1 |
| 12:30 | 1 | start | 1 | 3 | 4 | 2 |
| 12:31 | 1 | moving | 1 | 3 | 5 | 2 |
| 21:32 | 1 | moving | 1 | 4 | 6 | 2 |
| 21:33 | 1 | stop | 1 | 5 | 7 | 2 |
| 21:36 | 2 | start | 2 | 2 | 4 | 3 |
With the RideID I should be able to create a unique column by concatinating rideid with deviceid. With that colum I would than be able to filter in a dashboard.
Any tips or sugestions how to calculate the RideId colum?
thanks in advance KR Arjen
Hi AvanderKnijff ,
Group the table by stop time:
Column = var _end = CALCULATE(MIN('Table'[Time]),FILTER('Table','Table'[eventType]="stop"&&'Table'[Time]>=EARLIER('Table'[Time])&&'Table'[DeviceID]=EARLIER('Table'[DeviceID]))) return IF(ISBLANK(_end),'Table'[Time],_end)Then rank the group in same device ID.
Column = var _end = CALCULATE(MIN('Table'[Time]),FILTER('Table','Table'[eventType]="stop"&&'Table'[Time]>=EARLIER('Table'[Time])&&'Table'[DeviceID]=EARLIER('Table'[DeviceID]))) return IF(ISBLANK(_end),'Table'[Time],_end)Best Regards,
Jay
2 Replies
Hi AvanderKnijff ,
Group the table by stop time:
Column = var _end = CALCULATE(MIN('Table'[Time]),FILTER('Table','Table'[eventType]="stop"&&'Table'[Time]>=EARLIER('Table'[Time])&&'Table'[DeviceID]=EARLIER('Table'[DeviceID]))) return IF(ISBLANK(_end),'Table'[Time],_end)Then rank the group in same device ID.
Column = var _end = CALCULATE(MIN('Table'[Time]),FILTER('Table','Table'[eventType]="stop"&&'Table'[Time]>=EARLIER('Table'[Time])&&'Table'[DeviceID]=EARLIER('Table'[DeviceID]))) return IF(ISBLANK(_end),'Table'[Time],_end)Best Regards,
Jay
Hi Jay,
Thanks for your help, I managed to do the group by. This realy helps! I'm able to do a filter based on time.
The ranking would be helpfull to be able to list the start and end of a given ride on one row. However, the given ranking formula is a duplicate of the grouping. Could you share the ranking formula or do I misunderstood your solution?
First, step now is to understand the first formula 🙂
Thanks in advance, KR Arjen
