topic Re: Determine start and end coordinates for each distinct trip. in DAX Commands and Tips

Determine start and end coordinates for each distinct trip.

Anonymous — Tue, 24 Nov 2020 02:10:54 GMT

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.

Re: Determine start and end coordinates for each distinct trip.

amitchandak — Tue, 24 Nov 2020 02:31:03 GMT

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

Re: Determine start and end coordinates for each distinct trip.

Anonymous — Tue, 24 Nov 2020 04:35:46 GMT

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

Re: Determine start and end coordinates for each distinct trip.

amitchandak — Tue, 24 Nov 2020 06:28:02 GMT

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

Re: Determine start and end coordinates for each distinct trip.

Anonymous — Wed, 25 Nov 2020 02:08:35 GMT

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

Re: Determine start and end coordinates for each distinct trip.

Anonymous — Thu, 26 Nov 2020 00:54:38 GMT

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