Skip to main content
cancel
Showing results for 
Search instead 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

Reply
MWitkin
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
MartinMehaudy
Helper I
Helper I

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!

lalthan
Resolver II
Resolver II

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/

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.

 

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

 

 

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

Sure, but they question was
"I am trying to find a way to calculate the driving distance between any given two points that are on the map visual."

So i assumed that the goal is to select two points on the map and then get the distance in a measure.
Using the approach you posted you would still have to Pre calculate all possible combinations to achieve this.

Ohh, OK. I didn't know your requirement was for dynamic co-ordinates

MartinMehaudy
Helper I
Helper I

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 found the solution for the drive distance between 2 points please let me know 

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

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!

fso
Advocate II
Advocate II

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

 

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!

Helpful resources

Announcements
Fabric Community Conference

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.