cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

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

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; 

 

Distance to Closest Cinema (Miles) =
VAR HouseLatitude = 'Route Information'[Lattitude]
VAR HouseLongitude = 'Route Information'[Longitude]
VAR EarthCircumference = 3959
VAR P =
DIVIDE ( PI (), 180 )
Return
MINX('Cinema Locations',

VAR CinemaLatitude = 'Cinema Locations'[Lattitude]
VAR CinemaLongitude = 'Cinema Locations'[Longitude]

return

ACOS(
SIN(HouseLatitude * P )*SIN(CinemaLatitude * P )
+COS(HouseLatitude * P )*COS(CinemaLatitude * P )
*COS((CinemaLongitude * P)-(HouseLongitude * P )))
 
* EarthCircumference
)

 

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.  

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
dedelman_clng New Contributor
New Contributor

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

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

 

miles.png

 

 

View solution in original post

37 REPLIES 37
dedelman_clng New Contributor
New Contributor

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

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

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

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 New Contributor
New Contributor

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

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

 

 

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

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 New Contributor
New Contributor

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

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.

 

 

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

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. 

Example.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

dedelman_clng New Contributor
New Contributor

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

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. 

Super User
Super User

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

Hey guys. Hope you don't mind my partaking in what sounds like an interesting conversation

 

@Joshua_Peter

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?

 

@dedelman_clng

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.   

 

 

dedelman_clng New Contributor
New Contributor

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

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).

 

David

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,687)