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 )
VAR CinemaLatitude = 'Cinema Locations'[Lattitude]
VAR CinemaLongitude = 'Cinema Locations'[Longitude]
SIN(HouseLatitude * P )*SIN(CinemaLatitude * P )
+COS(HouseLatitude * P )*COS(CinemaLatitude * P )
*COS((CinemaLongitude * P)-(HouseLongitude * P )))
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.
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
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.
Closest HOUSE (Miles) test = VAR HOUSELatitude = 'Route Information'[Lattitude] VAR HOUSELongitude = 'Route Information'[Longitude] VAR EarthCircumference = 3959 VAR P = DIVIDE ( PI (), 180 ) Var P2 = DIVIDE(PI(), 360 ) VAR _ThisHOUSE = SELECTEDVALUE('Route Information'[MEMNO])
CALCULATE( MINX('Route Information (Dup'')',
VAR ClostestHOUSELatitude = 'Route Information (Dup'')'[Lattitude] VAR ClostestHOUSELongitude = 'Route Information (Dup'')'[Longitude]
You should be able to do the calculation against a single instance of 'Route Information'. Try this:
Closest HOUSE (Miles) test =
VAR HOUSELatitude = MAX('Route Information'[Lattitude])
VAR HOUSELongitude = MAX('Route Information'[Longitude])
VAR EarthCircumference = 3959
VAR P =
DIVIDE ( PI (), 180 )
VAR P2 =
DIVIDE ( PI (), 360 )
VAR _ThisHOUSE =
SELECTEDVALUE ( 'Route Information'[MEMNO] )
FILTER(ALL('Route Information'), 'Route Information'[MEMNO] <> _ThisHOUSE), //Note the use of FILTER
VAR ClostestHOUSELatitude = 'Route Information'[Lattitude]
VAR ClostestHOUSELongitude = 'Route Information'[Longitude]
FILTER(ALL('Route Information'), 'Route Information'[MEMNO] <> _ThisHOUSE), //Note the use of FILTER ROUND (
2 * EarthCircumference
* ASIN (
SIN ( ( ClostestHOUSELatitude - HOUSELatitude ) * P2 ) ^ 2
+ COS ( HOUSELatitude * P )
* COS ( ClostestHOUSELatitude * P )
* SIN ( ( ClostestHOUSELongitude - HOUSELongitude ) * P2 ) ^ 2
// 'Route Information'[MEMNO] <> _ThisHOUSE //moving this up to the table on MINX and adding ALL
I tested on some Lat-Long data that I had and it definitely changes values (that are not 0) when I click on a spot on a map (I can't vouch for the formula's accuracy, but at least it changes). Basically I added the MAX at the top, removed the duplicate table, and moved the filter into each call to 'Route Information', and wrapped ALL() around the table so it will iterate over the entire table regardless of which point is selected.
Okdokie we are making some progress no 0 values like before, thanks Dedelman!
Not sure on whether its accurate though in its calculation;
So along with the Closest House (Miles) columns now using the formula you provided below i have this Closest House column which is returning the refference for this closest point so there is some usable context;
Closest House = CALCULATE(MAX('Route Information (Dup'')'[MEMNO]), FILTER('Route Information (Dup'')', ROUND(2 * 3959 * ASIN(SQRT( SIN(('Route Information (Dup'')'[Lattitude] - 'Route Information'[Lattitude]) * PI()/360)^2 + COS('Route Information'[Lattitude] * PI()/180) * COS('Route Information (Dup'')'[Lattitude] * PI()/180) * SIN(('Route Information (Dup'')'[Longitude] - 'Route Information'[Longitude]) * PI()/360)^2)), 1) = 'Route Information'[Closest House (Miles)]))
Now this formula before just returned every MEMNO the same as the MEMNO we were looking up the closest value for, which was expected as before the distance was Zero.
Now its returning the odd few, stradorically but not sure on the validity of the closest house as i can see with some filters its a bit random.
So in this exmaple we can see it saying closest store to A05400 is 145.6 miles and that house ref is R15700.
So on the bottom map we are just seeing A05400 and R15700 then the top map is all the houses available in that area, highlighting in red what it classed as closest?
Also in the table we can see it thinks R15700 closest house is S55000 which just adds more mystery but is slightly plausable if S55000 was further north but still S55000 isn't the closest to R15700 anyway.
Been stairing at this for days now and you've already got better results than me just need to be able to rely on it 🙂
Since you're looking at a small scale, is it possible that the Lat/Long for all of those nearby house (per the map) are the same (if you don't have very precise lat/long measurements), and the formula is excluding them (thinking they're the same house) ? I know it shouldn't likely matter, but just a thought.
I'm curious about your approach and have a couple of questions:
a) Why are you using two nested MINXs? Wouldn't only one suffice, like in the initial example? I'm probably missing something.
b) I understand @Joshua_Peter needs a calculated column. Were you thinking of a measure? I'm asking because you are using SELECTEDVALUE( ) in the definition of _ThisHOUSE. If you were thinking of a calc column, why SELECTEDVALUE ( 'Route Information'[MEMNO] ) instead of simply 'Route Information'[MEMNO]?
c) Is the CALCULATE after the return necessary? I guess it's just the remnants of the first approach whose very last line you commented out? If it's so, I would take it out. Although I think in this case it would be fine since you are filtering over the whole 'Route Information' table, it might in general bring undesired effects through context transition.
Hi @AlB - welcome to the conversation. I was about to light up the bat signal as I had reached the end of my skills.
My code was actually @Joshua_Peter's code, just cleaned up with DAX formatter, and putting FILTER in the proper places. I've been trying to break the code down myself to help with the second issue but having no luck. I assumed it was for a measure, but yes I suppose he could be looking for a calculated column (no offense to @Joshua_Peter, but many times on these forums people are looking for "columns" when they really mean a measure to be placed in a table/matrix so I default to measure instinctively).