Forum Discussion
Measuring Distance to find closest point between points from a single table
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
Community ChampionInstead 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
- dedelman_clng
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
- dedelman_clng
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.