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



  • 4.  RE: Calculating Distances between Locations

    Bronze Contributor
    Posted Aug 30, 2018 03:31 PM
      |   view attached
    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)

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