Finding closest Clinic from a given postcode

[zuber85]

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 clinic, with a given post code.  

The clinics locations will grow overtime, so would need have the ability to increase them

Is this something that is possible?

[lbendlin]

Define "closest".  As the crow flies, or on a road network?  Which distances are we talking about? Can you use approximations based on the general location, or do you need to use the Great Circle calculation?

[zuber85]

Hi thanks for your reply.

Looking at distance, we can use crow flies and would like to use the Great circle calculation.

[lbendlin]

Sounds good. Please provide sample data that fully covers your issue.

[zuber85]

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

[lbendlin · Accepted solution]

distances are in km.

[zuber85]

And sorry, is it possible to have the results in Miles or is KM the only option?

[zuber85]

Wow, thanks.

Now what if I wanted it so that if I just enter a single postcode it will give me a result?  Like how you have a table of the results, I will be having over 160k post codes, so rather than a manual search a postcode is just entered

[lbendlin]

You can have a slicer. It won't be fast but it will work.  Personally I would use the filter pane with Advanced filtering.

Do I need to remind you of the KM to Miles conversion factor?  What type of mile?