Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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?
Solved! Go to Solution.
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?
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
And sorry, is it possible to have the results in Miles or is KM the only option?
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
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?