Forum Discussion
Solved
Finding closest Clinic from a given postcode
Hi, I need some help with making a PowerBi Report. We have 6 clinic post codes, and I would like to work a way of being able to tell me which is my closest clinic and the distance to that cli...
distances are in km.
Hi thanks for your reply.
Looking at distance, we can use crow flies and would like to use the Great circle calculation.
Sounds good. Please provide sample data that fully covers your issue.
I cannot upload a spreadsheet with my data however here is a small example:
This would be a list of ALL postcodes (currently 160,000 rows):
AB25 1HN AB25 2YT AB51 5DD EH39 4HH EH4 7HF EH46 7HZ EH54 7BA EH6 8JL FK2 0TS FK7 9NL G2 6UA G73 3HS G73 5DJ IV18 0AA IV36 1DT IV36 1EQ KY12 8QB ML11 9WQ PA15 2TL PH10 6JQ Then this would be my clinics table, which will grow and I will need to add more:
Clinic Post Code Name EH3 9AF EDINBURGH G1 1DQ GLASGOW AB12 3LF ABERDEEN DD2 5JT DUNDEE So, when a postcode is entered, it will look to see which clinic is closest from the clinic list and display the name of the clinic and how far it is,
for example if the post code was EH4 7HF then the response would show
CLINIC NAME: Edinburgh
Distance: 4.4 miles