Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

Going the Distance

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 = SELECTE...
Mathematical
Other
fran_parrett's avatar
fran_parrett
Frequent Visitor
3 years ago

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's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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's avatar
      fran_parrett
      Frequent Visitor
      3 years ago

      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.