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

     

     

     

AlB's avatar
AlB
Icon for Community Champion rankCommunity Champion
7 years ago

Anonymous

Cool to hear it works. I think it will need some minor modifications to respond to time periods (other than a single day) and other slicers.

By the way I had a quick look at the data you posted earlier comparing the actual distance and the distance with my rough calculation. It seems that 96 would be the best factor to apply, instead of the 80 that we have now. This is purely empirical though and strictly based in the limited sample you provided.

Anonymous's avatar
Anonymous
Not applicable
7 years ago

AlB

 

Yeah was just the start, I wanted to verify around 100 points and then go with the average factor from that sample should get us in the ballpark of accuracy I will need.

 

I have created a v3 of the sample file here.

 

To include a date column.

 

There is also the Closest House New column, which is a combination of the original closest house formula and the Distance to closest house formula you wrote to try and retrieve the associated nearest MEMNO that is that distance away. That would also be ideal as a measure to adjust with any on page slicers. 

 

Think I've learnt more depicting your guy's formulas this week than I have all year. 

 

Thanks again for the help!! 

 

Josh

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Just thought... 

     

    The closest House is a TOPN function... 

    TOPN(
            1,
            'Route Information',
            VAR Lat2 = 'Route Information'[Lattitude]
            VAR Lng2 = 'Route Information'[Longitude]
            //---- Algorithm here -----
                           
            VAR final =
                80*SQRT(POWER((Lat1-Lat2),2) + POWER((Lng1-Lng2),2))
            RETURN
                IF (
                final <> 0,
                final
            ),
            ASC
        )

    So currently it is returning the TOP 1st Value in ascending order. I need the second TOPN. 

     

    Going with the same theory as the; 

    IF (
                final <> 0,
                final
            )

    Piece the in distance to closest house working formula.