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.
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:
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!
Hi MWitkin, did you found a solution to your problem? If so please share, I am interested in learning as well.
Thanks and regards!
If you already have Latitude and Longtitude, the same can be easily calculated using Power Query. Check out this link for further details.
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.
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?
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.
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.
Check out a full recap of the month!
Check out the winners of the recent 'Can You Solve These?' community challenge!
Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.