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):
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] )
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 )
IF ( _DistanceFromCurrentRough <> 0, _DistanceFromCurrentRough )
As you can see, on 1/18/18, the distance is different for M49418
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- 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.
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.
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.
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.
VAR Lat2 = 'Route Information'[Lattitude]
VAR Lng2 = 'Route Information'[Longitude]
//---- Algorithm here -----
VAR final =
80*SQRT(POWER((Lat1-Lat2),2) + POWER((Lng1-Lng2),2))
final <> 0,
So currently it is returning the TOP 1st Value in ascending order. I need the second TOPN.
Going with the same theory as the;
final <> 0,
Piece the in distance to closest house working formula.