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

GoingTheDistance.pbix35 KB

Mathematical

Other

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

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