How to return a ZIP Code from a Power BI report/dashboard back to a table

Hi rwendeln,

yes if you have a table with the zipcodes available (or can create one) you could use a slicer to filter this table to a specific selection. With a dax function (like the SELECTEDVALUE() function) you could then use that specific selected zipcode in a measure where you establish the distance. 

Would that be an option for you?

Regards,

Tim

Tim,

Thanks for your quick response.  Here’s my specific application.

I have a client who has multiple stores across the nation.  He wants me to build a report/dashboard by which he can enter the ZIP code of a store and then see on a map all competitor stores within a certain mile radius, along with other performance analytics (such as the comparative sales performance of his other and competitive stores within the distance radius).

I built a prototype in Excel, which I’m trying to convert to Power BI.  The Excel model has two interactive tables to filter stores according to distance by ZIP code,  The first table contains columns for [store ID], [store name], [store ZIP code], [store longitude], and [store latitude], as well as columns for [selected ZIP code], [selected ZIP longitude], [selected ZIP latitude], and [miles calculation] (which calculates distance from selected ZIP code to each store based upon a formula using latitudes and longitudes).  The second table contains all ZIP codes nationally, along with their respective longitudes and latitudes.  When you enter the desired ZIP code into an input box (cell), the model looks up its longitude and latitude from the second table, and returns those to the first table—by which miles are then calculated by formula.

I’ve replicated the tables in Power BI.  I’ve created a report (part of a dashboard) to provide for a ZIP code entry.  But I can’t figure out how to return the ZIP code entry from the report back to my table structure, in order to look up the longitude and latitude of the selected ZIP code and return those to the [selected ZIP longitude] and [selected ZIP latitude] columns. 

In other words, while I know how to use report/dashboard slicers to filter tables for data visualizations, is there a way to return an entry from a report/dashboard back to the model dynamically (like Excel reads a value from an input cell)?  You had mentioned SELECTEDVALUE(), but I’m not sure the argument that would point the function to the input on the report/dashboard.  That is, can a report/dashboard entry “write back” to a table in Power BI?

Again, thanks for your advice.

Hi 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:

https://community.powerbi.com/t5/DAX-Commands-and-Tips/DAX-Dynamic-Measure-based-on-slicer-selection/m-p/2221018 

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien