Going the Distance 01-13-2023 03:40 AM

[Greg_Deckler]

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

[jaisveer21]

@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.

[Greg_Deckler]

@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.

[fran_parrett]

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.

[Greg_Deckler]

@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

[fran_parrett]

@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.

[alissa]

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

[MYDATASTORY]

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

[Greg_Deckler]

@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.