# Power BI Exchange

View Only

## Calculating Distances between Locations

• #### 1.  Calculating Distances between Locations

Posted Aug 29, 2018 04:26 PM
First - I'm relatively new to Power BI.  I am trying to build a report that will display the distance between a selected "From" zip code and other addresses within a selected distance (miles).  My fact table contains a long list of project addresses (city, state, zip) and other project-related data.  I have a lookup table that lists all U.S. zip codes along with the city, state, latitude, and longitude.

Ideally, I would select a "From" zip code and a "Distance" in miles, and the report would show me the list of sites that fall within the "Distance" or less.

I had some help creating a measure to do the distance math between two sets of latitude & longitude (see below).  However, I can't seem to get it to compute the distances for each row (record) in the project list.   I've attached some screenshots to show some of the structure and report <g class="gr_ gr_870 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" id="870" data-gr-id="870">screen</g>.
MEASURE -------------------------------------------------------------------------------------------------------------------------------------------
Miles =
var Lat1 = LOOKUPVALUE(ZipCodes[Zip_Latitude],ZipCodes[Zip_Code], min('Recruiting Test-2'[Zip_Code]))
var Lng1 = LOOKUPVALUE(ZipCodes[Zip_longitude],ZipCodes[Zip_Code], MIN('Recruiting Test-2'[Zip_Code]))

var Lat2 = LOOKUPVALUE(ZipCodes[Zip_longitude],ZipCodes[Zip_Code], From_Zip[From_Zip Value])
var Lng2 = LOOKUPVALUE(ZipCodes[Zip_longitude],ZipCodes[Zip_Code], From_Zip[From_Zip Value])
---- Algorithm here -----
var P = DIVIDE( PI(), 180 )
var A = 0.5 - COS((Lat2-Lat1) * p)/2 +
COS(Lat1 * p) * COS(lat2 * P) * (1-COS((Lng2- Lng1) * p))/2
var final = 12742 * ASIN((SQRT(A)))
return (final * 0.621371)
-----------------------------------------------------------------------------------------------------------------------------------------------------------
I know I'm missing something in how to select individual records for the measure, but not sure where to go to figure it out.  Any assistance would be greatly appreciated.

------------------------------
Dean Stanberry
Director-FM Services
Denver CO
720-877-1521
------------------------------

• #### 2.  RE: Calculating Distances between Locations

Bronze Contributor
Posted Aug 30, 2018 02:49 AM

Hi,

I worked on some similar but did all my distance calculations in SQL Server using spatial functions, and then presented in Power BI. you can read about my approach here - Mapping Adventures in Power BI

There is a nice video here on calculating distance between 2 points as well - Calculate the distance between two points/locations/coordinates in Power BI

Hopefully these help
Cheers
John

------------------------------
John Broomfield

------------------------------

• #### 3.  RE: Calculating Distances between Locations

Posted Aug 30, 2018 10:45 AM
This looks like something I might like to address over Labor Day weekend. Please feel free to share anything else relevant. mberg@kpikc.com

------------------------------
Marshall Berg
Kansas City, MO
8165851238
------------------------------

• #### 4.  RE: Calculating Distances between Locations

Bronze Contributor
Posted Aug 30, 2018 03:31 PM
Dean,

If I understand your request, in your report you'd like to be able to control your "Miles" measure with a slicer (single selection of a desired zip code). Have a look at the attach file to if this is what you're after.

Del

------------------------------
Del Swingle
WA
------------------------------

Attachment(s)

Mileage.pbix   1.00 MB 1 version

• #### 5.  RE: Calculating Distances between Locations

Posted Dec 06, 2018 04:37 PM
Del,
Your file is perfectly doing what I had in mind.  Would you mind replying to my email address and helping me convert your file to use the type of data I have?  It is very close.

Thank you,
Brandon
brandon.west@shawinc.com

------------------------------
Brandon West