Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
  • 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

  • 2.  RE: Calculating Distances between Locations

    Bronze Contributor
    Posted Aug 30, 2018 02:49 AM


    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

    John Broomfield
    Business Intelligence Analyst


  • 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

  • 4.  RE: Calculating Distances between Locations

    Bronze Contributor
    Posted Aug 30, 2018 03:31 PM
      |   view attached

    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 Swingle


    Mileage.pbix   1.00 MB 1 version

  • 5.  RE: Calculating Distances between Locations

    Posted Dec 06, 2018 04:37 PM
    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 West
    Business Analyst II
    Shaw Industries
    Chatsworth GA