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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Employee
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
Employee
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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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