Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Measuring Distance to find closest point between points from a single table

Hi All, 

 

Hoping someone may know how to include a FILTER to remove itself as a possible answer. 

 

For the example i am trying to find the closest neighbour to a house in a single table, i have the full addresses including Longitude and Lattitude. 

 

I am trying to find the next closest point to itself using the Haversine formula but excluding itself ultimely, but really struggling to remove its own coordinates as a possible answer and returning the closest point as 0 miles. 

 

So at the moment i have one working formula that shows the closest cinema but this is using two tables so i have the House points table and the Cinema Address table and it finds the closest Cinema for examples sake. Using the Formula below; 

 

Distance to Closest Cinema (Miles) =
VAR HouseLatitude = 'Route Information'[Lattitude]
VAR HouseLongitude = 'Route Information'[Longitude]
VAR EarthCircumference = 3959
VAR P =
DIVIDE ( PI (), 180 )
Return
MINX('Cinema Locations',

VAR CinemaLatitude = 'Cinema Locations'[Lattitude]
VAR CinemaLongitude = 'Cinema Locations'[Longitude]

return

ACOS(
SIN(HouseLatitude * P )*SIN(CinemaLatitude * P )
+COS(HouseLatitude * P )*COS(CinemaLatitude * P )
*COS((CinemaLongitude * P)-(HouseLongitude * P )))
 
* EarthCircumference
)

 

I now need to calculate the closest neighbour to the house addresses. 

 

So i created a duplicate of the Route Information table so i have two tables containing the same information, then run the exact formula above but removing the Cinema Locations table for the Route Information duplicate table. 

 

This then returns 0 miles as the closest distance as itself is the closest distance. 

 

So how do i filter out itself as a possible answer? 

 

Something like IF(AND(lattitude = lattitude, longitude = longitude), move to next closest would be the best way just no idea how to achieve that so hoping someone far more versed with DAX can help me out. 

 

Thanks, 

 

Josh.  

 

 

Need Help
  • dedelman_clng's avatar
    dedelman_clng
    7 years ago

    OK, I took AlBs code and Anonymouss last post and turned it into a measure.  First, I recreated 'Route Information' to have 5 dates' worth of data, one date of which is missing 3 of the locations (Dt is literally just a list of 5 dates 1/14 - 1/18):

     

    Route Information =
UNION (
    CROSSJOIN (
        SUMMARIZE (
            'Table2 (2)',
            'Table2 (2)'[MEMNO],
            'Table2 (2)'[Post Code],
            'Table2 (2)'[Lattitude],
            'Table2 (2)'[Longitude]
        ),
        FILTER ( Dt, Dt[Date] < "1/18/18" )
    ),
    CROSSJOIN (
        SUMMARIZE (
            FILTER (
                'Table2 (2)',
                NOT ( 'Table2 (2)'[MEMNO] IN { "A21200", "A22701", "B10500" } )
            ),
            'Table2 (2)'[MEMNO],
            'Table2 (2)'[Post Code],
            'Table2 (2)'[Lattitude],
            'Table2 (2)'[Longitude]
        ),
        FILTER ( Dt, Dt[Date] = "1/18/18" )
    )
)

    Then taking the working code, transformed it into a measure, keeping the Date filter (I'm probably not doing it the most efficient way using variables - I still have trouble getting ALLEXCEPT and KEEPFILTER working without trial and error):

     

    DCH (Miles) =
VAR HouseLatitude = MIN ( 'Route Information'[Lattitude] )
VAR HouseLongitude = MIN ( 'Route Information'[Longitude] )
VAR EarthCircumference = 3959
VAR P = DIVIDE ( PI (), 180 )
VAR House = SELECTEDVALUE ( 'Route Information'[MEMNO] )
VAR __Dt = SELECTEDVALUE ( 'Route Information'[Date] )
RETURN
    MINX (
        FILTER (
            ALL ( 'Route Information' ),
            'Route Information'[MEMNO] <> House
                && 'Route Information'[Date] = __Dt
        ),
        VAR CinemaLatitude = 'Route Information'[Lattitude]
        VAR CinemaLongitude = 'Route Information'[Longitude]
        VAR _DistanceFromCurrentRough =
            80 * SQRT ( POWER ( ( HouseLatitude - CinemaLatitude ), 2 ) +  POWER ( ( HouseLongitude - CinemaLongitude ), 2 )
                )
        RETURN
            IF ( _DistanceFromCurrentRough <> 0, _DistanceFromCurrentRough )
    )

    As you can see, on 1/18/18, the distance is different for M49418

     

     

     

37 Replies

  • dedelman_clng's avatar
    dedelman_clng
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Instead of having two tables, can you add a column to the single table indicating if a location record is a house or a theater? Then you can build a filter on that column within your calculations.

     

    Hope this helps

    David

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Dedelman, 

       

      The calculation for closest theater is working correctly at the moment, but i need another column to find the closest house. 

      So there is the primary Route Information table with a column for Names which is a unique identifier, along with Postal address and longitude, lattitude points. 

       

      I want to find the closest house to each house excluding its own postal information so the distance is never 0 miles. 

      I only made the duplicate table which is hidden in report view to duplicate the working formula for closest theater, just modifying the refferenced columns to use the 'duplicate table. 

       

      There is a unique identifier column those for house refferences so we could filter on that to exclude itself as the destination but how would i build that filter into the formula i am using? 

       

      Thanks, 

       

      Josh

      • dedelman_clng's avatar
        dedelman_clng
        Icon for Community Champion rankCommunity Champion
        7 years ago

        Try something like this

         

        Closest House =
  var __ThisHouse = SELECTEDVALUE(House[Name])

//Then for the other variables
        CALCULATE([your code here], House[Name] <> __ThisHouse)
        or
        CALCULATE([your code here], FILTER(House, House[Name] <> __ThisHouse)

        Because SELECTEDVALUE will return (BLANK) if more than one house is selected, this will not work at any aggregate level, but it sounds like you're pretty much wanting to do this on a single house anyway.

         

        Hope this helps

        David