Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

Calculating (adding) distance between multiple locations using longitude and latitude

Hi all,

 

Any help with the following would be great as it is an unique situation. A quick intro into the problem, 

 

1) I have teams who work at different locations each day, and there's a latitude and longitiude for each address and a post code

2) On the PBI dashboard the end user wants to select any team/s and date/s to show the total average distance. For example Mr ABC did 3 jobs today; (1st Town to 2nd Town + 2nd Town to Last Town)/3

3) I have attempted many solutions;

    -  Create two tables such that there's a lat, long, lat 1 and long 1 for measures

    -  Used a measure below, however nothing works

4) This dataset is linked to the main datasey by an unique identifier DATE&NAME

 

Any help would be super appreciated, thanks in advance.

Kilometers =
var Lat1 = MIN('From City'[lat])
var Lng1 = MIN('From City'[lng])

var Lat2 = MIN('To Cities'[lat])
var Lng2 = MIN('To Cities'[lng])
---- Algorithm here -----
var P = DIVIDE( PI(), 180 )
var A = 0.5 - COS((Lat2-Lat1) * p)/2 + 
    COS(Lat1 * p) * COS(lat2 * P) * (1-COS((Lng2- Lng1) * p))/2
var final = 12742 * ASIN((SQRT(A)))
return fina

 

1 ACCEPTED SOLUTION
v-kelly-msft
Community Support
Community Support

Hi @Anonymous ,

 

You could use Latitude and Longitude to calculate the distance between two locations with following formula:

 

=acos(sin(lat1)*sin(lat2)+cos(lat1)*cos(lat2)*cos(lon2-lon1))*6371

 

Then sum up the distances by each person and divide 3 to get the result you need.

ps: 6371 is Earth radius in km.

 

You can achieve it via either Power Query or DAX method.

 

Or you can use Google Map API .

You could also check the reference below:

https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?MessageKey=a90b6419-9bd3-439c-8100-1a5f1f4729a3&CommunityKey=b35c8468-2fd8-4e1a-8429-322c39fe7110&tab=digestviewer

https://stackoverflow.com/questions/27928/calculate-distance-between-two-latitude-longitude-points-haversine-formula

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

View solution in original post

6 REPLIES 6
v-kelly-msft
Community Support
Community Support

Hi @Anonymous ,

 

You could use Latitude and Longitude to calculate the distance between two locations with following formula:

 

=acos(sin(lat1)*sin(lat2)+cos(lat1)*cos(lat2)*cos(lon2-lon1))*6371

 

Then sum up the distances by each person and divide 3 to get the result you need.

ps: 6371 is Earth radius in km.

 

You can achieve it via either Power Query or DAX method.

 

Or you can use Google Map API .

You could also check the reference below:

https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?MessageKey=a90b6419-9bd3-439c-8100-1a5f1f4729a3&CommunityKey=b35c8468-2fd8-4e1a-8429-322c39fe7110&tab=digestviewer

https://stackoverflow.com/questions/27928/calculate-distance-between-two-latitude-longitude-points-haversine-formula

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

Anonymous
Not applicable

Hi,

 

I'm unsure how to apply the above soluiton to my situation as follows:

 

1) I have a columns of; team names, long', lat' dates, unique identifier, address

2) Each team can do 3-4 jobs a day each with it's own long, lat

3) I need to show the total and average distance for team/s on any date or date ranges

 

Thanks in advance

Anonymous
Not applicable

Hi, 

 

Can anyone help with the above?

 

Thanks

Anonymous
Not applicable

Hi,

 

I'm unsure how to apply the above soluiton to my situation as follows:

 

1) I have a columns of; team names, long', lat' dates, unique identifier, address

2) Each team can do 3-4 jobs a day each with it's own long, lat

3) I need to show the total and average distance for team/s on any date or date ranges

 

Thanks in advance

 

 

 

 

 

amitchandak
Super User
Super User

Anonymous
Not applicable

Morning Amit,

 

Thanks for your reply.

 

The formula in my post does not give the right answer. I will look at your links now and update you.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.