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.
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.
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.
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.
Hm, that explains how to grab the data in M, but not how to select 2 locations in the report and the query will then request the distance.
So I assume the fact that there's not a real solution out there yet means that you would have to pre-calculate all routes and then write a measure that simply gets the distance value from the table when a countrows-measure equals 2.
Dear @fso, If you read the blog posts fully, the latitude and longitude is replaced at the end with input parameter. So, the original query is finally converted to a function which accepts latitude and longitude as input parameter. This function can be used on any new data by defining the latitude field name and longitude field name in the function's parameter
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?
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!
Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.
Click here to read more about the December 2021 Updates!
Mark your calendars and join us for our next Power BI Dev Camp!