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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anam
Regular Visitor

Distance Calculation in Power BI

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?

3 ACCEPTED SOLUTIONS
cs_skit
Resolver IV
Resolver IV

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

View solution in original post

v-sihou-msft
Microsoft Employee
Microsoft Employee

@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,

View solution in original post

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

 

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

15 REPLIES 15
MAAbdullah47
Helper V
Helper V

But how can we integrate it to Power bi?

v-sihou-msft
Microsoft Employee
Microsoft Employee

@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,

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.

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

Can I have the distances data exported out in Excel?

cs_skit
Resolver IV
Resolver IV

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

But How to Integrate it into power bi?

Anonymous
Not applicable

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.

How do I use Google Maps API in Power BI?

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

 

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

This worked awesome, thank you!!!!

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
Not applicable

Hello, 

 

Did you get any answers? I have similar problem 🙂 

 

Thanks.

Fantastic blog, worked great for me

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.