- Power BI forums
- Updates
- News & Announcements
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Custom Visuals Development Discussion
- Health and Life Sciences
- Power BI Spanish forums
- Translated Spanish Desktop
- Power Platform Integration - Better Together!
- Power Platform Integrations (Read-only)
- Power Platform and Dynamics 365 Integrations (Read-only)
- Training and Consulting
- Instructor Led Training
- Dashboard in a Day for Women, by Women
- Galleries
- Community Connections & How-To Videos
- COVID-19 Data Stories Gallery
- Themes Gallery
- Data Stories Gallery
- R Script Showcase
- Webinars and Video Gallery
- Quick Measures Gallery
- 2021 MSBizAppsSummit Gallery
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- Events
- Ideas
- Custom Visuals Ideas
- Issues
- Issues
- Events
- Upcoming Events
- Community Blog
- Power BI Community Blog
- Custom Visuals Community Blog
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

- Power BI forums
- Forums
- Get Help with Power BI
- Desktop
- Re: Distance calculations - how many suppliers wit...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Distance calculations - how many suppliers within X Kms

06-17-2018
09:47 PM

I was able to calculate dynamically distnace between two points thanks using the following DAX, thanks to a post on Radacad Blog

Kilometers = var Lat1 = MIN('From City'[lat]) var Lng1 = MIN('From City'[lng]) var Lat2 = MIN('To Cities'[lat]) var Lng2 = MIN('To Cities'[lng]) ---- Algorithm here ----- var P = DIVIDE( PI(), 180 ) var A = 0.5 - COS((Lat2-Lat1) * p)/2 + COS(Lat1 * p) * COS(lat2 * P) * (1-COS((Lng2- Lng1) * p))/2 var final = 12742 * ASIN((SQRT(A))) return final

However I need to calculate the number of suppliers within X Km of a suburb, I have the lat & long of all the suburbs and suppliers.

Struggling to get something to work... any assistance with this would be greatly appreciated...

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

06-17-2018
11:46 PM

Hi @DamienW

Here's a mock-up of how I might do it.

- I've assumed Supplier and Suburbs sit in two tables like this:
Create a measure

**Suburb Supplier Distance**that computes the distance between a single Supplier & Suburb (same formula as you had):Suburb Supplier Distance = IF ( AND ( HASONEVALUE ( Supplier[Supplier] ), HASONEVALUE ( Suburb[Suburb] ) ), VAR Lat1 = SELECTEDVALUE ( Supplier[Latitude] ) VAR Lng1 = SELECTEDVALUE ( Supplier[Longitude] ) VAR Lat2 = SELECTEDVALUE ( Suburb[Latitude] ) VAR Lng2 = SELECTEDVALUE ( Suburb[Longitude] ) VAR P = DIVIDE ( PI (), 180 ) VAR A = 0.5 - COS ( ( Lat2 - Lat1 ) * p ) / 2 + COS ( Lat1 * p ) * COS ( lat2 * P ) * ( 1 - COS ( ( Lng2 - Lng1 ) * p ) ) / 2 VAR final = 12742 * ASIN ( ( SQRT ( A ) ) ) RETURN final )

- Create a Distance parameter table with a single column Distance, used to select the threshold distances.
- Create this measure to count Suppliers within a selected distance of the selected Suburb:
Number of Suppliers within Selected Distance of Suburb = VAR MinDistance = MIN ( Distance[Distance] ) VAR MaxDistance = MAX ( Distance[Distance] ) RETURN COUNTROWS ( FILTER ( Supplier, [Suburb Supplier Distance] >= MinDistance && [Suburb Supplier Distance] <= MaxDistance ) )

This is written so that you can have both lower and upper bounds on the distance, but you may want to rewrite with just an upper bound on the distance.

Well that's how I would do it. It may have to be adapted depending how your tables are structured.

Regards,

Owen

6 REPLIES 6

Anonymous

Not applicable

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

11-02-2020
06:37 AM

Great post...I was wondering if you would know how to convert this to miles instead of km? Also, the number in VAR final = '12742', what is the number referencing to? Thanks again.

Anonymous

Not applicable

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

06-25-2019
02:46 AM

Hi,

I have the same scenario in my work, i have calculated distance as sugeasted but along with this i need to show supplier along with all the suburbs in a particular distance from it in one world map.

can anyone please guide me . how can i achieve it .

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-08-2019
03:12 AM

Hi @OwenAuger, thank you for your solution. I was wondering if you can assist me further. I have a similar scenario however my distances have been calculated via the Google API as a function. How would I then dynamically be able to see the closest supplier. Thanks.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-08-2019
08:50 PM

@Waynesaaiman could you share detail of your tables or a PBIX with your current data model, and how you want the report to behave?

I'm assuming you want certain visuals to be filtered to the closest supplier to another selected location?

We should be able to do this with some sort of measure that filters suppliers down to just the closest one.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

06-17-2018
11:46 PM

Hi @DamienW

Here's a mock-up of how I might do it.

- I've assumed Supplier and Suburbs sit in two tables like this:
Create a measure

**Suburb Supplier Distance**that computes the distance between a single Supplier & Suburb (same formula as you had):Suburb Supplier Distance = IF ( AND ( HASONEVALUE ( Supplier[Supplier] ), HASONEVALUE ( Suburb[Suburb] ) ), VAR Lat1 = SELECTEDVALUE ( Supplier[Latitude] ) VAR Lng1 = SELECTEDVALUE ( Supplier[Longitude] ) VAR Lat2 = SELECTEDVALUE ( Suburb[Latitude] ) VAR Lng2 = SELECTEDVALUE ( Suburb[Longitude] ) VAR P = DIVIDE ( PI (), 180 ) VAR A = 0.5 - COS ( ( Lat2 - Lat1 ) * p ) / 2 + COS ( Lat1 * p ) * COS ( lat2 * P ) * ( 1 - COS ( ( Lng2 - Lng1 ) * p ) ) / 2 VAR final = 12742 * ASIN ( ( SQRT ( A ) ) ) RETURN final )

- Create a Distance parameter table with a single column Distance, used to select the threshold distances.
- Create this measure to count Suppliers within a selected distance of the selected Suburb:
Number of Suppliers within Selected Distance of Suburb = VAR MinDistance = MIN ( Distance[Distance] ) VAR MaxDistance = MAX ( Distance[Distance] ) RETURN COUNTROWS ( FILTER ( Supplier, [Suburb Supplier Distance] >= MinDistance && [Suburb Supplier Distance] <= MaxDistance ) )

This is written so that you can have both lower and upper bounds on the distance, but you may want to rewrite with just an upper bound on the distance.

Well that's how I would do it. It may have to be adapted depending how your tables are structured.

Regards,

Owen

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

06-18-2018
01:19 AM

Thanks, worked a treat, ended up ditching the distance parameter table, and just created three measures with the distances that i wanted...

E.g

Suppliers in 5km = COUNTROWS (

FILTER (

Supplier,

[Kilometers] <= 5))

Announcements

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

Check out the April 2024 Power BI update to learn about new features.

Featured Topics

Top Solution Authors

User | Count |
---|---|

108 | |

96 | |

75 | |

63 | |

55 |

Top Kudoed Authors

User | Count |
---|---|

138 | |

102 | |

89 | |

86 | |

64 |