Distance Calculation in Power BI

[Anam]

Hey,

I wanted to know that once I have plotted several locations on a map in Power BI, how can I calculate the distance between any two locations?

[cs_skit · Accepted solution]

do you have LAT and LONG? If yes you can do it with math:

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

if not you need to custom call Bing Maps or Google Maps APIs for that

[v-sihou-msft · Accepted solution]

@Anam

You need 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

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 as @cs_skit suggested.

Regards,

[MAAbdullah47]

But how can we integrate it to Power bi?

[v-sihou-msft · Accepted solution]

@Anam

You need 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

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 as @cs_skit suggested.

Regards,

[CJ7]

Cant You just do euclidean distance -> sqrt((lat1-lat2)^2+(lon1-lon2)^2)*110.574 km ? 

Also Why do wee need to get geocode from other sources like Google ( paid ), when power BI does locate cities on the map - therefore it could just give us direct answer regarding the longitude and latitude of certain city.

[JJH_]

@Anam What's the metric (KM, M, CM) that should apply to the result of applying this formula?

[oolamide85]

Can I have the distances data exported out in Excel?

[cs_skit · Accepted solution]

do you have LAT and LONG? If yes you can do it with math:

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

if not you need to custom call Bing Maps or Google Maps APIs for that

[MAAbdullah47]

But How to Integrate it into power bi?

[Anonymous]

How about if you have 3 tables, one telling where from shipped, one telling where to shipped and third one which is having cities and latitube and longitudes? how to combine all of those.

[Anam]

How do I use Google Maps API in Power BI?

[Phil_Seamark · Accepted solution]

I did a blog post on calculating the direct line distance between two points in DAX

http://radacad.com/dynamic-distances-in-power-bi

[RickWald]

This worked awesome, thank you!!!!

[MAAbdullah47]

Thank you for this post, If I want to use ArcGIS map and I pointed on a City then I want to determine the nearest cities within (x) kilometers radius how can we do this? 

[Anonymous]

Hello, 

Did you get any answers? I have similar problem 🙂 

Thanks.

[GuySev]

Fantastic blog, worked great for me

[ShrikantKhanna]

This post is great but can anyone assist in calculating distance between two postcodes ?