Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

[HELP NEEDED] : Distance calculation with (latlong) in same table/column.

Bonsoir BI community ! Anyone ever tried the calculating distance using long lat in same columns before? I am working on an academic project and want to calculate the distance btween two stores per ...
  • Greg_Deckler's avatar
    Greg_Deckler
    5 years ago

    Anonymous Just use Going the Distance: Going the Distance - Microsoft Power BI Community

    Like this:

    Column = 
    VAR __NextLocation = MINX(FILTER('Table',[Country]=EARLIER([Country]) && [Location] > EARLIER([Location])),[Location])
    VAR __FromLat = [lat]
    VAR __ToLat = MINX(FILTER('Table15',[Country]=EARLIER([Country]) && [Location] = __NextLocation),[lat])
    VAR __FromLong = [lng]
    VAR __ToLong = MINX(FILTER('Table15',[Country]=EARLIER([Country]) && [Location] = __NextLocation),[lng])
    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
  • Greg_Deckler's avatar
    Greg_Deckler
    5 years ago

    Anonymous Try:

    Column = 
    VAR __NextLocation = MAXX(FILTER('Table',[Country]=EARLIER([Country]) && [Location] < EARLIER([Location])),[Location])
    VAR __FromLat = [lat]
    VAR __ToLat = MINX(FILTER('Table15',[Country]=EARLIER([Country]) && [Location] = __NextLocation),[lat])
    VAR __FromLong = [lng]
    VAR __ToLong = MINX(FILTER('Table15',[Country]=EARLIER([Country]) && [Location] = __NextLocation),[lng])
    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
Anonymous's avatar
Anonymous
Not applicable
5 years ago

 

Greg_Deckler 

Sorry I am coming back on this as I noticed an error I made during my review. I think I need a slight help further. In fact, from Location 1 to Location 2 (as location 1 has no previous initial value, the distance will is zero, but the result is the other way around). I have tried to re-write the query but not getting the answer, please can you help me?

 

 

 

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Anonymous Try:

    Column = 
    VAR __NextLocation = MAXX(FILTER('Table',[Country]=EARLIER([Country]) && [Location] < EARLIER([Location])),[Location])
    VAR __FromLat = [lat]
    VAR __ToLat = MINX(FILTER('Table15',[Country]=EARLIER([Country]) && [Location] = __NextLocation),[lat])
    VAR __FromLong = [lng]
    VAR __ToLong = MINX(FILTER('Table15',[Country]=EARLIER([Country]) && [Location] = __NextLocation),[lng])
    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
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Greg_Deckler  Thanks, worked perfectly! I dropped you a message inbox. Thanks.