cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jackCoD
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_idtimestamplatitudelongitude
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_idtimestamplatitudelongitude
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
jackCoD
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

View solution in original post

5 REPLIES 5
amitchandak
Super User IV
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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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

jackCoD
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

View solution in original post

amitchandak
Super User IV
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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors