Tracking dashboard

[AvanderKnijff]

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

[Anonymous · Accepted solution]

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

[AvanderKnijff]

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