cancel
Showing results for
Did you mean:
Frequent Visitor

## Determine start and end coordinates for each distinct trip.

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.

1 ACCEPTED SOLUTION
Frequent Visitor

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

5 REPLIES 5
Super User IV

@jackCoD , 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

Proud to be a Super User!

Frequent Visitor

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

Frequent Visitor

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

Super User IV

@jackCoD , 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

Proud to be a Super User!

Frequent Visitor

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

Announcements