Forum Discussion
Display Stores Within Radius for Multiple ZIP Codes
Hi everyone,
I am working on a Power BI report using Azure Maps.
I have a STORE table that contains:
- Store name
- Address
- Latitude
- Longitude
I also have a ZIPCODE table that contains all valid ZIP codes along with their corresponding coordinates.
In the report, I created an input slicer that allows users to give multiple ZIP codes.
My requirement is:
For each selected ZIP code, I want to display all stores within a given radius r on the Azure Map visual.
So essentially:
- The user inputs one or more ZIP codes in the slicer.
- For each selected ZIP code, the system should calculate distances between that ZIP code’s coordinates and all store locations.
- All stores within radius r should be displayed on the map.
I’m relatively new to Microsoft Fabric, so any suggestions or assistance would be greatly appreciated.
7 Replies
It's technically trivial (using the Haversine formula) but it has limited value. The center of a ZIP code may not have any business meaning, and the Great Circle distance may not have a business meaning either. What you would need is a real time driving distance/time route engine connection - and that becomes expensive quickly.
What problem are you trying to solve with that visual?
- I’ve already used the Haversine formula to calculate the distance between a single coordinate and a set of stores and I can successfully display all stores within a given radius r. However, I’m struggling to extend this logic to handle multiple user‑entered ZIP codes.My goal is to identify all potential stores located within a radius r of each user‑provided ZIP code.How can I efficiently compute this for multiple ZIP code inputs?
- I’ve already used the Haversine formula to calculate the distance between a single coordinate and a set of stores and I can successfully display all stores within a given radius r. However, I’m struggling to extend this logic to handle multiple user‑entered ZIP codes.My goal is to identify all potential stores located within a radius r of each user‑provided ZIP code.How can I efficiently compute this for multiple ZIP code inputs?
You create a CROSSJOIN between the stores and the ZIP codes, and then calculate the Haversine for each of the items in the cartesian product. You can also use GENERATE for the same purpose.
You will want to limit that to the filter context (for example what is visible on the map) to avoid computational overload.
I tried using both CROSSJOIN and GENERATE, but it keeps throwing the following error:
"You cannot use a calculated table to reference a Direct Lake table. The model includes a calculated table, which is not supported. Please remove the calculated table."
Is there any workaround for this limitation?Try doing the processing in a measure rather than a calculated table.
- I’ve created the following measure to calculate the Haversine distance between locations in the stores table and the selected ZIP codes from the zipcode table. However, when I display this measure in a table or matrix visual, the distances shown are incorrect. Additionally, my requirement is to display the calculated distances both on Azure Maps and in a table visual for all user‑selected ZIP codes in my report. Could you explain how I can achieve this behavior properly?
DistanceToPLZ =
MINX(
CROSSJOIN(
stores,
SELECTCOLUMNS(
ALLSELECTED(zipcode),
"GeoLat", zipcode[lat],
"GeoLon", zipcode[long]
)
),
VAR Lat1 = stores[Latitude]
VAR Lon1 = stores[Longitude]
VAR Lat2 = [GeoLat]
VAR Lon2 = [GeoLon]VAR R = 6371
VAR dLat = RADIANS(Lat2 - Lat1)
VAR dLon = RADIANS(Lon2 - Lon1)VAR a =
SIN(dLat/2)^2 +
COS(RADIANS(Lat1)) *
COS(RADIANS(Lat2)) *
SIN(dLon/2)^2VAR c = 2 * ATAN(DIVIDE(SQRT(a),SQRT(1-a)))
RETURN R * c
)
