Distance between ALL locations per city

[Anonymous]

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

[Anonymous]

I have managed to get all the combinations of distances between two shops in a city by crossjoining the main table and a referenced one. The problem is almost solved. How can I use the custom invoked function (DistancesAPI) to run in a new column of my crossjoined table?

[v-juanli-msft · Accepted solution]

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
Maggie

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

[Anonymous]

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