cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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

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,

Employee

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

Proud to be a Datanaut!

15 REPLIES 15
Helper V

But how can we integrate it to Power bi?

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,

New Member

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.

Frequent Visitor

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

Frequent Visitor

Can I have the distances data exported out in Excel?

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

Helper V

But How to Integrate it into power bi?

Frequent Visitor

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.

Regular Visitor

How do I use Google Maps API in Power BI?

Employee

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

Proud to be a Datanaut!

Frequent Visitor

This worked awesome, thank you!!!!

Helper V

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

Helper II

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

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors