cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Calculating the maximum distance across multiple rows?

Hello,

I have a bit of a complicated question about calculating distances across rows. I wanted to see if you can help out. First, I will explain the data and goal then go over an example of what I'm trying to do.

 

Sample data

sem1_0-1606317785223.png

JourneyEventVehicleEventTimeLatitudeLongitude
1231555Disconnected07/11/2020 19:00:12515.6
1232444Disconnected07/11/2020 19:00:12515.5
1233333Disconnected07/11/2020 19:01:12515.3
1234555Restored07/11/2020 19:21:05515.5
9995888Disconnected09/11/2020 11:07:03515.7
9996777Disconnected09/11/2020 11:07:50515.4
9997888Restored09/11/2020 11:14:36515.5
9998777Restored09/11/2020 11:17:26515.6

 

We have multiple journeys which contain multiple events that occur on different vehicles. Each event has a time it was created as well as coordinates of where it was created.

 

Goal

What I need to do: whenever there is a "Disconnected" event, I need to know what the distance of that vehicle is with the other vehicles and show the maximum of those distances.

 

Important

  • There can be 1 or more vehicles per journey
  • The "disconnected" event can occur more than once per journey per vehicle (so for example, once in the morning, maybe again an hour later).
  • The math for distance between coordinates is not difficult, but the formula is long so I will simplify how I represent it in the below example by using DISTANCE[lat1, long1, lat2, long2].
  • Additionally, as I think if you can sum or take the difference of values across the rows, you can probably calculate the distance as well, so the important thing to answer here is not "what formula gives me distances?" it's "how do I apply these formulas across the rows?"

Example with sample data

sem1_1-1606318430328.png

  • If we look at the first sample journey, 123, we see there are 3 vehicles (555, 444, 333).
  • A "Disconnected" event has occured around 19:00 (events 1, 2, 3).
  • For event 1 (row 1) I would like to know the maximum of the distances that vehicle has from the others:
    • Event 1 vs Event 2 distance would be DISTANCE[51, 5.6, 51, 5.5]. Let's assume it's 50 km
    • Event 1 vs Event 3 distance would be DISTANCE[51, 5.6, 51, 5.3]. Let's assume it's 60 km
  • That means on the row for event 1 I would like to have a column with the value of 60.
  • Note: important to remember that there could be another "Disconnected" event later on, for example 30 minutes later, where we would want to repeat this same calculation

 

Does anyone have any ideas?

1 ACCEPTED SOLUTION
Microsoft
Microsoft

Hi  @sem1 ,

According to your description, create the data and set the formula rule to [latitude] + [longitude].

v-yangliu-msft_0-1606468267355.png

1. Create Measure.

Measure =
var _table=GENERATE(SELECTCOLUMNS(FILTER(ALL('Table'),[Journey]=MAX([Journey])),"jo1",[Journey],"Type",[Vehicle],"juli",[Longitude]+[Latitude]),SELECTCOLUMNS(FILTER(ALL('Table'),[Journey]=MAX([Journey])),"jo2",'Table'[Journey],"Type2",[Vehicle],"juli2",[Longitude]+[Latitude]))

var _value=
MAXX(_table,ABS([juli]-[juli2]))

return
IF(MAX('Table'[Event.1])="Disconnected",_value,BLANK())

2. Result.

v-yangliu-msft_1-1606468267360.jpeg

You can downloaded PBIX file from here.

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
Microsoft
Microsoft

Hi  @sem1 ,

According to your description, create the data and set the formula rule to [latitude] + [longitude].

v-yangliu-msft_0-1606468267355.png

1. Create Measure.

Measure =
var _table=GENERATE(SELECTCOLUMNS(FILTER(ALL('Table'),[Journey]=MAX([Journey])),"jo1",[Journey],"Type",[Vehicle],"juli",[Longitude]+[Latitude]),SELECTCOLUMNS(FILTER(ALL('Table'),[Journey]=MAX([Journey])),"jo2",'Table'[Journey],"Type2",[Vehicle],"juli2",[Longitude]+[Latitude]))

var _value=
MAXX(_table,ABS([juli]-[juli2]))

return
IF(MAX('Table'[Event.1])="Disconnected",_value,BLANK())

2. Result.

v-yangliu-msft_1-1606468267360.jpeg

You can downloaded PBIX file from here.

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors