Determine start and end coordinates for each distinct trip.

[Anonymous]

I need to determine the Start and End (i.e. first/earliest) coordinates based on a timestamped trip data.

My table is as follows:

trip_id	timestamp	latitude	longitude
1	.... 06:00 AM	....1000	....500
1	.... 06:01 AM	....1001	....499
1	.... 06:02 AM	....1001	....498
2	.... 07:30 AM	....1001	....399
2	.... 07:31 AM	....1002	....399
2	.... 07:32 AM	....1002	....398

The starting location is therefore:

trip_id	timestamp	latitude	longitude
1	.... 06:00 AM	....1000	....500
2	.... 07:30 AM	....1001	....399

I need a similar result for the end location. I can then plot the latitude & longitudes on a Map widget.

[amitchandak]

@Anonymous , I given the name what there in the sample. Please select the correct column name in your power bi file. Or share a sample pbix

[Anonymous]

Hi 

I did substitute my own parameters into the measure. 

Are you able to clarify your original solution more? The instruction is somewhat unclear.

Thank you

[Anonymous · Accepted solution]

For anyone else visiting this thread; My solution ended up looking like this:

I create 2 x measures,

• One for the Earliest date of a trip_id (i.e. start of trip)

Earliest date =
CALCULATE(MIN('Table'[date]),ALLEXCEPT('Table','Table'[trip_id]))

Another for Latest date of a trip_id (i.e. end of trip): 

Latest date =
CALCULATE(MAX('Table'[date]),ALLEXCEPT('Table','Table'[trip_id]))

Lastly, I believe I inserted a custom DAX column. This checks whether [date] is:

• The start time of a trip
• The end time of a trip
• or a somewhere inbetween (a waypoint)
• The DAX contains a nested IF function, and assigns values based on [date]'s value:

date_check = IF('Table'[date] = [Latest date], "End Trip", IF('Table'[date] = [Earliest date], "Start Trip", "Waypoint"))

These steps could probably be contained in one DAX expression - but I would rather keep it separated so I can use the measures for other things.

Cheers

[amitchandak]

@Anonymous , add a measure like this and all other un summarized column

calculate(min(Table[timestamp]), filter(Table, Table[timestamp] = calculate(min(Table[timestamp]), allexcept(Table, Table[trip_id]))))

Or create measure for all other columns then trip_id

[Anonymous]

Hi @amitchandak 

I'm getting the error: 

A single value for column 'timestamp' in table 'Table' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single results.

---

Regards