cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Can PBI Desktop calculate the driving distance between points on a map visual?

Hello,

I am working on a Power BI project in which I have many locations taken from an Excel spreadsheet and are plotted on a map BI visual using their latitude and longitude. 

 

 

I am trying to find a way to calculate the driving distance between any given two points that are on the map visual. This would preferably be done entirely in BI rather than in Excel as the client would prefer we not make changes to their spreadsheet.

 

Is there an easy way to produce a dashboard that can calculate and return the driving distance between any two selected points on the map? 

 

I know that Power BI does it's mapping via Bing Maps so I am hoping their is a way to take advantage of Bing Maps' more robust functionality in BI.

 

Thanks in advance.

 

 

14 REPLIES 14
Highlighted
Advocate II
Advocate II

Re: Can PBI Desktop calculate the driving distance between points on a map visual?

I believe this is not something that is integrated in the current map feature.
However, in PBI Desktop you could for example create a query to the Google Maps API to get the directions (maybe it works with BingMaps, too).
It's quite simple, just do a "from web" query to a URL like this, where you simply have to provide the lat/long of start and destination:

http://maps.googleapis.com/maps/api/directions/json?origin=48.1351253,11.5819806&destination=50.1109...

 

Highlighted
Helper I
Helper I

Re: Can PBI Desktop calculate the driving distance between points on a map visual?

Hi fso, I'm very interested in learning how to use queries for/from maps such as Bing or Google. Do you know some nice tutorials?

Just to give an overview on my "level", I am pretty much an IT noob outside simple Excel formulas and pivot tables. I am just learning to query with PowerBI (never used Access before).

 

Thanks in advance and best regards!

Highlighted
Helper I
Helper I

Re: Can PBI Desktop calculate the driving distance between points on a map visual?

Hi MWitkin, did you found a solution to your problem? If so please share, I am interested in learning as well.

Thanks and regards!

Highlighted
Impactful Individual
Impactful Individual

Re: Can PBI Desktop calculate the driving distance between points on a map visual?

@fso @MartinMehaudy Did either of you find a solution to this question?  Thanks, Tom

Highlighted
Resolver II
Resolver II

Re: Can PBI Desktop calculate the driving distance between points on a map visual?

If you already have Latitude and Longtitude, the same can be easily calculated using Power Query.  Check out this link for further details.

 

http://www.girlswithpowertools.com/2014/05/distance/

Highlighted
Advocate II
Advocate II

Re: Can PBI Desktop calculate the driving distance between points on a map visual?

I believe what MWitkin is looking for is not the 'as the crow flies' distance, but the actual driving distance.
You can create an M function that takes origin and destination as parameters and returns the driving distance from an API like Google Maps. However, for this to work I think you would have to precalculate all possible combinations using Get Data (M).

 

I am not aware of a way to make DAX expressions provide input to M functions... or how you would grab data from the web using DAX only.

Highlighted
Helper I
Helper I

Re: Can PBI Desktop calculate the driving distance between points on a map visual?

Hi Tom, no luck so far. Another user posted a solution for when you have Latitude and Longitude, but in my data source all I've got is a large list of addresses.

I did found this post, http://analystcave.com/excel-calculate-distances-between-addresses/, which I believe will lead me in the right direction. I'm looking for someone within my organization who knows about VBA and APIs to assist me (I'm a noob in both, any programming at all for the case).

Hopefully, it might also be helpful for fso.

 

By the way, I know nothing about APIs. Is it a licence you have to pay for?

 

Thanks everybody!

Helper I
Helper I

Re: Can PBI Desktop calculate the driving distance between points on a map visual?

Hello everybody, I found this link, http://analystcave.com/excel-calculate-distances-between-addresses/ , and I believe I'll move over to Excel for this particular problem. Hopefully it will help more people, and maybe someone can find a way to work with APIs within Power BI. Being a total noob on VBA, APIs or anything programming, I'll look for help within my organization.

Thanks everybody!

Highlighted
Resolver II
Resolver II

Re: Can PBI Desktop calculate the driving distance between points on a map visual?

The below post explains how to get address details in Power BI using  Google Map API. I believe road distance can be similarly calculated using the Distance Matrix API.

 

http://biinsight.com/power-bi-and-google-maps-api-address-lookup/

 

 

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors