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;
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.
Solved! Go to Solution.
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
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
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
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
Hi,
Tried the below as a caluclated column;
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])
return
CALCULATE(
MINX('Route Information (Dup'')',
VAR ClostestHOUSELatitude = 'Route Information (Dup'')'[Lattitude]
VAR ClostestHOUSELongitude = 'Route Information (Dup'')'[Longitude]
return
MINX('Route Information (Dup'')'
, ROUND(2 * EarthCircumference *
ASIN(SQRT(
SIN((ClostestHOUSELatitude - HOUSELatitude) * P2)^2 +
COS(HOUSELatitude * P) * COS(ClostestHOUSELatitude* P) *
SIN((ClostestHOUSELongitude - HOUSELongitude) * P2)^2)), 1)))
, 'Route Information (Dup'')'[MEMNO] <> _ThisHOUSE)
This still returns a 0 miles value, any ideas?
This is still based on the two table model, does it need to be one table?
Thanks,
Josh
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] ) RETURN CALCULATE ( MINX ( FILTER(ALL('Route Information'), 'Route Information'[MEMNO] <> _ThisHOUSE), //Note the use of FILTER VAR ClostestHOUSELatitude = 'Route Information'[Lattitude] VAR ClostestHOUSELongitude = 'Route Information'[Longitude] RETURN MINX ( FILTER(ALL('Route Information'), 'Route Information'[MEMNO] <> _ThisHOUSE), //Note the use of FILTER
ROUND ( 2 * EarthCircumference * ASIN ( SQRT ( SIN ( ( ClostestHOUSELatitude - HOUSELatitude ) * P2 ) ^ 2 + COS ( HOUSELatitude * P ) * COS ( ClostestHOUSELatitude * P ) * SIN ( ( ClostestHOUSELongitude - HOUSELongitude ) * P2 ) ^ 2 ) ), 1 ) ) ) // '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;
Formula;
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.
Any ideas?
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 🙂
Thanks again in advance!
Cheers
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.
Hey guys. Hope you don't mind my partaking in what sounds like an interesting conversation
@Anonymous
Can you share the pbix or at least a fragment of it with a data sample that can be used for testing?
Just to double check: Are you sure the formula to calculate the distance between to points (Harversine) is working correctly and not the issue?
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 @Anonymous 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 @Anonymous'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 @Anonymous, 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).
David
We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.
Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!
User | Count |
---|---|
118 | |
84 | |
67 | |
50 | |
36 |