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

[dedelman_clng · Accepted solution]

OK, I took @AlBs code and @Joshua_Peters 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]

@Joshua_Peter

Hmmm... I'm a bit surprised it's accurate as I just put it together quite quickly and only with rough numbers. I mean I wouldn't trust my formula if I were you Anyway we can keep using it until all other issues are solved and then we could try to change it if necessary as that wouldn't affect the rest.

I was going to post the measure but I see @dedelman_clng has already done it. 

[AlB]

@Joshua_Peter

What are you going to need regarding the dates? Filter only a single day? A period?

@dedelman_clng

Out of curiosity, what problems did you have with ALLEXCEPT and KEEPFILTERS ?

[dedelman_clng]

@AlB- mainly wrapping my head around the advanced context filter concepts (extended tables, etc). 20+ years of SQL development has me more comfortable with assigning and using variables. I've taken the Mastering DAX class but some of the concepts are having trouble sticking in my brain.  When I get advanced concepts to work it feels more like luck than skill because I'd have a hard time explaining it to someone else.

[Joshua_Peter]

@AlB

Yeah i mean i've only checked 10 points so far, but initially it looks accurate for a total dataset. 

House Ref	Returned Closest House	Returned Distance	Closest House Ref	Actual Distance
A05402		1.53	D15201	2.6
A05703		0.83	M94655	0.9
A23400		1.51	S73500	2
A27202		1.41	M94774	2.2
B01701		3.49	C01307	4.2
B14800		2.36	W14600	2.8

There out to different degrees of variation but aslong as there within a +/- 5 mile radius i should be able to achieve the desired result i am aiming for. 

Plus i am verifying as a car would drive between points on Google Maps and not as the bird would fly so i would expect the actual to be a degree more. 

@AlB

I want to inititally see the data for a full week with closest house to see anyone who is not within 30 miles of another house point to be classed as a "remote location". 

Then Filter down to a single date and see again if there is anyone with a greater distance than 30 miles, that isnt categorised as a "remote location" so in theory if that house point was on a different date it could be within 30 miles of the next house if that makes sense just to visulise a theory and analyse by specific dates and by weeks. 

I would like to filter and distances be recalculated on a range on variables aswell like Town/City would be one to remove certain points from the analysis overall as we have some points in Belfast it is throwing some issues as it doesn't account for the water crossing so i need to split those by Island and Mainland to avoid any issues later. 

@dedelman_clng 

I have just tried out the measure from above and its calculating distances but sporadically across the range so some rows in the table visual have enteries and some dont?

@dedelman_clng Ammendment. 

The measure is working, must of been my impatience while it populated the data returned. 

Yet to verify the entries it has produced though against actual distances aswell but will take a look to see if its a similar finding to the calculated column. 

Thanks again, 

Josh

[AlB]

@Joshua_Peter

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.

[Joshua_Peter]

@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

[Joshua_Peter]

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.