Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Dean Stanberry]

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 screen.
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
——————————

[John Broomfield]

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
Business Intelligence Analyst

——————————
——————————————-

[Marshall Berg]

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
——————————
——————————————-

[Del Swingle]

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
——————————
——————————————-

[Brandon West]

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
Business Analyst II
Shaw Industries
Chatsworth GA
706-532-6052
——————————
——————————————-