skip to main content
Power BI
    • What is Power BI
    • Why Power BI
    • Customer stories
    • Data visuals
    • Security
    • Power BI Desktop
    • Power BI Pro
    • Power BI Premium
    • Power BI Mobile
    • Power BI Embedded
    • Power BI Report Server
  • Pricing
    • Azure + Power BI
    • Microsoft 365 + Power BI
    • Dynamics 365 + Power BI
      • Energy
      • Healthcare
      • Manufacturing
      • Media
      • Retail
    • For analysts
    • For IT
      • Overview
      • Embedded analytics
      • Power BI visuals
      • Automation
      • Documentation
      • Community
    • Getting started
      • Overview
      • Self-guided learning
      • Webinars
      • Documentation
      • Roadmap
      • Overview
      • Issues
      • Give feedback
    • Blog
    • Business intelligence topics
    • Overview
    • Forums
    • Galleries
    • Submit ideas
    • Events
    • User groups
    • Community blog
    • Register
    • ·
    • Sign in
    • ·
    • Help
    Go To
    • Microsoft Power BI Community
    • Welcome to the Community!
    • News & Announcements
    • Get Help with Power BI
    • Desktop
    • Service
    • Report Server
    • Power Query
    • Mobile Apps
    • Developer
    • DAX Commands and Tips
    • Custom Visuals Development Discussion
    • Health and Life Sciences
    • Power BI Spanish Community
    • Translated Spanish Desktop
    • Power Platform Integration - Better Together!
    • Power Platform Integrations
    • Power Platform and Dynamics 365 Integrations
    • Training and Consulting
    • Instructor Led Training
    • Galleries
    • Community Connections & How-To Videos
    • COVID-19 Data Stories Gallery
    • Themes Gallery
    • Data Stories Gallery
    • R Script Showcase
    • Webinars and Video Gallery
    • Quick Measures Gallery
    • 2021 MSBizAppsSummit Gallery
    • 2020 MSBizAppsSummit Gallery
    • 2019 MSBizAppsSummit Gallery
    • Events
    • Ideas
    • Custom Visuals Ideas
    • Issues
    • Issues
    • Events
    • Upcoming Events
    • Community Engagement
    • T-Shirt Design Challenge 2023
    • Community Blog
    • Power BI Community Blog
    • Custom Visuals Community Blog
    • Community Support
    • Community Accounts & Registration
    • Using the Community
    • Community Feedback
    cancel
    Turn on suggestions
    Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
    Showing results for 
    Search instead for 
    Did you mean: 
    • Microsoft Power BI Community
    • Galleries
    • Quick Measures Gallery
    • Going the Distance

    Going the Distance

    03-07-2020 11:43 AM - last edited 05-13-2020 00:06 AM

    Super User Greg_Deckler
    Super User
    7493 Views
    LinkedIn LinkedIn Facebook Facebook Twitter Twitter
    Greg_Deckler
    Super User Greg_Deckler
    Super User
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    Going the Distance

    ‎03-07-2020 11:43 AM

    Uses the Haversine formula to compute the distance between the latitudes and longitudes of two points.

     

     

    c = 
        VAR __FromCity = SELECTEDVALUE('From City'[City])
        VAR __ToCity = SELECTEDVALUE('To City'[City])
        VAR __FromLat = LOOKUPVALUE('Table'[Latitude],'Table'[City],__FromCity)
        VAR __ToLat = LOOKUPVALUE('Table'[Latitude],'Table'[City],__ToCity)
        VAR __FromLong = LOOKUPVALUE('Table'[Longitude],'Table'[City],__FromCity)
        VAR __ToLong = LOOKUPVALUE('Table'[Longitude],'Table'[City],__ToCity)
        VAR __distanceLong = RADIANS(__ToLong - __FromLong)
        VAR __distanceLat = RADIANS(__ToLat - __FromLat)
        VAR __a = (SIN(__distanceLat/2))^2 + COS(RADIANS(__FromLat)) * COS(RADIANS(__ToLat)) * SIN((__distanceLong/2))^2
        VAR __y = SQRT(__a)
        VAR __x = SQRT(1 - __a)
        VAR __atan2 = 
            SWITCH(
                TRUE(),
                __x > 0, ATAN(__y/__x),
                __x < 0 && __y >= 0, ATAN(__y/__x) + PI(),
                __x < 0 && __y < 0, ATAN(__y/__x) - PI(),
                __x = 0 && __y > 0, PI()/2,
                __x = 0 && __y < 0, PI()/2 * (0-1),
                BLANK()
            )
        VAR __c = 2 * __atan2
    RETURN
        __c

     

     

    c is the main measure and then you need to multiple this by the radius of the earth in either km, miles, etc.

     

    Note, this does not account for the elliptical shape of the Earth so don't use it for anything besides estimation as you could be off a few miles over long distances.

     

    eyJrIjoiNWYwYTBjZjEtZGIxNi00NWExLWI5MzMtNjJlZDg5MTA1ZDU2IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9


    @ me in replies or I'll lose your thread!!!
    Instead of a Kudo, please vote for this idea
    Become an expert!: Enterprise DNA
    External Tools: MSHGQM
    YouTube Channel!: Microsoft Hates Greg
    Latest book!:
    Mastering Power BI 2nd Edition

    DAX is easy, CALCULATE makes DAX hard...
    GoingTheDistance.pbix
    173 KB
    Labels:
    • Labels:
    • Mathematical
    • Other
    Message 1 of 9
    7,493 Views
    4
    Reply
    • All forum topics
    • Previous Topic
    • Next Topic
    jaisveer21
    jaisveer21
    New Member
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎05-31-2023 11:23 PM

    @Greg_Deckler What do you mean by - c is the main measure and then you need to multiple this by the radius of the earth in either km, miles, etc.????
    Isn't this the complete solution or there is something we have to multiply at the end after this whole piece of code?

    Also for me, it shows '0' in the distance calculation.
    Note- i have only 1 table having both to & from locations in the same table and I am using 'SELECTEDVALUE' intead of 'LOOKUP' for column reference still it gives me 0.

    Message 8 of 9
    1,015 Views
    0
    Reply
    Greg_Deckler
    Super User Greg_Deckler
    Super User
    In response to jaisveer21
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎06-01-2023 05:44 AM

    @jaisveer21 Download the PBIX associated with this post and you will see how to use the measure. I made it generic so that you could use it for either miles or kilometers.


    @ me in replies or I'll lose your thread!!!
    Instead of a Kudo, please vote for this idea
    Become an expert!: Enterprise DNA
    External Tools: MSHGQM
    YouTube Channel!: Microsoft Hates Greg
    Latest book!:
    Mastering Power BI 2nd Edition

    DAX is easy, CALCULATE makes DAX hard...
    Message 9 of 9
    1,005 Views
    0
    Reply
    fran_parrett
    fran_parrett
    Frequent Visitor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎01-13-2023 03:40 AM

    Thank you @Greg_Deckler this is brilliant and works well. I have an issue where I am using "From" and "To" postcodes but in some cases I do not know the "To" postcode (so that field is blank). This is then giving me a ridiculously huge number. Do you have a suggestion for how I can get it to either give me a figure of 0 or say "unknown" if one of the postcodes is unknown? Any help would be much appreciated. I'm still new to DAX and can't figure out the best way to do this.

    Message 5 of 9
    2,081 Views
    0
    Reply
    Greg_Deckler
    Super User Greg_Deckler
    Super User
    In response to fran_parrett
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎01-13-2023 05:59 AM

    @fran_parrett Couple thoughts, let's say that one of your postcodes is blank and that is a substitute for City in the example. You could do this:

    c = 
        VAR __FromCity = SELECTEDVALUE('From City'[City])
        VAR __ToCity = SELECTEDVALUE('To City'[City])
        VAR __FromLat = LOOKUPVALUE('Table'[Latitude],'Table'[City],__FromCity)
        VAR __ToLat = LOOKUPVALUE('Table'[Latitude],'Table'[City],__ToCity)
        VAR __FromLong = LOOKUPVALUE('Table'[Longitude],'Table'[City],__FromCity)
        VAR __ToLong = LOOKUPVALUE('Table'[Longitude],'Table'[City],__ToCity)
        VAR __distanceLong = RADIANS(__ToLong - __FromLong)
        VAR __distanceLat = RADIANS(__ToLat - __FromLat)
        VAR __a = (SIN(__distanceLat/2))^2 + COS(RADIANS(__FromLat)) * COS(RADIANS(__ToLat)) * SIN((__distanceLong/2))^2
        VAR __y = SQRT(__a)
        VAR __x = SQRT(1 - __a)
        VAR __atan2 = 
            SWITCH(
                TRUE(),
                __x > 0, ATAN(__y/__x),
                __x < 0 && __y >= 0, ATAN(__y/__x) + PI(),
                __x < 0 && __y < 0, ATAN(__y/__x) - PI(),
                __x = 0 && __y > 0, PI()/2,
                __x = 0 && __y < 0, PI()/2 * (0-1),
                BLANK()
            )
        VAR __c = 2 * __atan2
        VAR __Result = IF( __FromCity = BLANK() || __ToCity = BLANK(), 0, __c)
    RETURN
        __Result

    @ me in replies or I'll lose your thread!!!
    Instead of a Kudo, please vote for this idea
    Become an expert!: Enterprise DNA
    External Tools: MSHGQM
    YouTube Channel!: Microsoft Hates Greg
    Latest book!:
    Mastering Power BI 2nd Edition

    DAX is easy, CALCULATE makes DAX hard...
    Message 6 of 9
    2,072 Views
    1
    Reply
    fran_parrett
    fran_parrett
    Frequent Visitor
    In response to Greg_Deckler
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎01-13-2023 06:22 AM

    @Greg_Deckler thank you so much. Yes the city was substituted for postcodes as I needed it on a much more local scale. This worked perfectly though! I really appreciate your help.

    Message 7 of 9
    2,069 Views
    2
    Reply
    alissa
    alissa
    Frequent Visitor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎09-08-2021 09:51 AM

    Thank you, @Greg_Deckler !! This worked perfectly for me. Much appriciated! 

    Message 4 of 9
    5,585 Views
    2
    Reply
    MYDATASTORY
    MYDATASTORY Resolver I
    Resolver I
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎03-07-2020 12:07 PM

    @Greg_Deckler  This is great, looking forward to seeing the final distance calculation.

    Message 2 of 9
    7,489 Views
    0
    Reply
    Greg_Deckler
    Super User Greg_Deckler
    Super User
    In response to MYDATASTORY
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎03-07-2020 12:40 PM

    @MYDATASTORY 

     

    OK, I downloaded the UK Postal Code latitudes and longitudes from here:

    https://www.freemaptools.com/download-uk-postcode-lat-lng.htm

     

    You guys sure have a lot of postal codes, apparently about 1.7M of them and since they are unique as well as the latitudes and longitudes, well, it makes for a fairly sizeable file. Anyway, I went ahead and implemented a few columns and such to get the distances.

     

    However, they only allow a maximum upload file size of 50MB and the file is nearly twice that. So I am uploading to my personal OneDrive and will share out a file from there. Hang tight.

     

     


    @ me in replies or I'll lose your thread!!!
    Instead of a Kudo, please vote for this idea
    Become an expert!: Enterprise DNA
    External Tools: MSHGQM
    YouTube Channel!: Microsoft Hates Greg
    Latest book!:
    Mastering Power BI 2nd Edition

    DAX is easy, CALCULATE makes DAX hard...
    Message 3 of 9
    7,484 Views
    0
    Reply

    Power Platform

    • Overview
    • Power BI
    • Power Apps
    • Power Pages
    • Power Automate
    • Power Virtual Agents

    • Sign in
    • Sign up

    Browse

    • Solutions
    • Consulting Services

    Downloads

    • Power BI Desktop
    • Power BI Mobile
    • Power BI Report Server
    • See all downloads

    Learn

    • Guided learning
    • Documentation
    • Support
    • Community
    • Give feedback
    • Webinars
    • Developers
    • Blog
    • Newsletter

    © 2023 Microsoft

    Follow Power BI

    • Privacy & cookies
    • Manage cookies
    • Terms of use
    • Trademarks
    Consumer Privacy Act (CCPA) Opt-Out Icon Your Privacy Choices