Forum Discussion
Distance between ALL locations per city
Hello,
I have a table with shop names, address, city, lat/long. I want to get the distances between all the shops per city.
That would mean:
City: Eindhoven
Shop 1 <-> Shop 2
Shop 1 <-> Shop 3
Shop 2 <-> Shop 3
. . .
City: Amsterdam
Shop 2 <-> Shop 3
Shop 2 <-> Shop 4.
. . .
I'm using the Google Maps API through a function which takes the Origin & Destination to give the distance.
I was thinking about writing a FOREACH() statement which would check If city=city and then give distances between all the shops within that city but I'm not really sure how to build the part where it has to call the function.
I have tried running the function with Origin as concatinated lat/long and the Destination with the same but it returns 1 meter for every row, which means it is getting the distance from and to the exact same shop for every shop.
Any help would be very appreciated.
Kind regards,
Vasil Apostolov
Hi Anonymous
As searched, i can't find any API to give the distance based on your requirement.
There is a workaround you may check:
After using Google Map API with Power BI, you can get dataset with columns like "country","city","lan/long",,ect.
Then calcualte distance for shops in Power BI.
If you'd like to display Points within a Distance Radius on a Power BI Map, please refer to the blog.
If you'd like to show distance among shops for each city in a table/matrix visual which can be filtered by slicer, please refer to:
https://community.powerbi.com/t5/Desktop/Distance-Calculation-in-Power-BI/td-p/206979
https://radacad.com/dynamic-distances-in-power-bi
Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi Anonymous
As searched, i can't find any API to give the distance based on your requirement.
There is a workaround you may check:
After using Google Map API with Power BI, you can get dataset with columns like "country","city","lan/long",,ect.
Then calcualte distance for shops in Power BI.
If you'd like to display Points within a Distance Radius on a Power BI Map, please refer to the blog.
If you'd like to show distance among shops for each city in a table/matrix visual which can be filtered by slicer, please refer to:
https://community.powerbi.com/t5/Desktop/Distance-Calculation-in-Power-BI/td-p/206979
https://radacad.com/dynamic-distances-in-power-bi
Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi v-juanli-msft ,
This points me towards the rights direction:
If you'd like to show distance among shops for each city in a table/matrix visual which can be filtered by slicer, please refer to:
https://community.powerbi.com/t5/Desktop/Distance-Calculation-in-Power-BI/td-p/206979
https://radacad.com/dynamic-distances-in-power-bi
However, I arrive to the point where I have my Google Distances API and I want to run it for all possible distances between all the shops within a city. I noticed that it is not possible to invoke a custom function from one table and get the Origin from another table. So I am stuck again.
Kind regards,
Vasil Apostolov