Forum Discussion
How to return a ZIP Code from a Power BI report/dashboard back to a table
Community SupportHi rwendeln ,
When you calculate distance ,you need Latitude and Longitude .And the Latitude and Longitude are needed to calculate the distance between two locations with following formula:
=acos(sin(lat1)*sin(lat2)+cos(lat1)*cos(lat2)*cos(lon2-lon1))*6371 (Ps: 6371 is Earth radius in km.)
You may also achieve it via either Power Query or DAX method. Or you can use Google Map API .
About How to convert zip codes to latitude and longitude，you can download zip code and corresponding lat and long values for different countries from here: (this is open source data and free of charge)
http://download.geonames.org/export/dump/
You can then import the data into your database and query it.
And refer the following links:
https://stackoverflow.com/questions/11354490/latitude-and-longitude-based-on-zip-code
https://gis.stackexchange.com/questions/28035/how-to-convert-zip-codes-to-latitude-and-longitude
And about slicer，get the lat, long of the site in the same fashion:
VAR IDLat = SELECTEDVALUE(EventTable[Lat])
VAR IDLong = SELECTEDVALUE(EventTable[Long])
VAR SiteLat = SELECTEDVALUE(SitesTable[Lat])
VAR SiteLong = SELECTEDVALUE(SitesTable[Long])
refer:
Did I answer your question? Mark my post as a solution!
Best Regards
Lucien
Lucien,
Thanks for your thorough response on calculating distance using ZIP code and the resulting longitude and latitude. However, since I'm already set up to do that, my problem is a little different. Here's what I need to do.
I want to create a Power BI report that filters a table of competitors based upon a 150-mile radius of the ZIP code that my client enters into the report. I have three tables:
- Competitors: Contains all competitors, their ZIP codes, and data for the report table.
- ZIPLookUp: Contains the single ZIP code to be looked up and its corresponding latitude and longitude.
- ZIPCodes: Contains all domestic ZIP codes, their corresponding latitudes, longitudes, city, state, and county; also includes the [EntryLatitude] and [EntryLatitude] to be populated from ZIPLookUp, and the formula that calculates miles between the entry ZIP code and all domestic ZIP codes.
Here are the steps I've constructed:
1. Client enters a ZIP code into the report:
2. The ZIP code entered on the report populates the [entry_ZIP] in the ZIPLookUp table. In turn, based on the ZIP code, the [entry_Latitude] and [entry_Longitude] in the ZIPLookUp table are populated from the ZIPCodes table.
3. From this, the [EntryLatitude] and [EntryLongitude] columns from the ZIPLookUp table populate the corresponding columns in the ZIPCodes table. The formula for calcuating distance beween pairs of latitudes/longitudes results in the [miles] column in the ZIPCodes table.
4. Based upon ZIP code, the Competitors table is filtered for those competitors within a 150-mile radius and the data for those competitors is used in the report table (as seen in #1).
My question is: How can I feed the ZIP code that is entered into the report (step 1) into the ZIPLookUp table (step 2)? That is, is it possible to dynamically populate a table (in this case ZIPLookUp) from a report entry? (And, if not, is there another way to achieve my purpose?)
Many thanks for any assistance.
Ron