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

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

@dedelman_clng

No offence taken, I am trying to create the solution as a calculated column as i wanted to later filter the results for houses with the closest neighbour more than 20 miles away and recently had issues filtering visuals based on a measure in the desktop application since the last update. (like IF(FY = This FY, 1, 0 as an example, this FY been the measure to display the fiscal year based on Today() in ruined the visuals so was opting for a calculated column to avoid this issue). 

 

I tried what @AlB mentioned removing the SELECTEDVALUE () and am now back to displaying 0 mile values. 

 

Closest House (Miles) =
VAR HouseLatitude = MIN('Route Information'[Lattitude])
VAR HouseLongitude = MIN('Route Information'[Longitude])
VAR EarthCircumference = 3959
VAR P =
DIVIDE ( PI (), 180 )
VAR P2 =
DIVIDE ( PI (), 360 )
VAR _ThisHouse = MIN('Route Information'[MEMNO])

VAR ClostestHouseLatitude = MIN('Route Information'[Lattitude])
VAR ClostestHouseLongitude = MIN('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
)
)

 

On the variables if I switch MIN for MAX all the distances are 3000 miles + 

If I keep MAX on the HouseLatitude variable and none on the ClosestHouseLatitude the results vary but are fictional 

&& if I drop the min / max on both we're back to 0 miles again. 

 

@dedelman_clng

 

In my example, I was zoomed into that area to display the area but overall there are around 13000 points across the UK. 

 

@AlB

 

I could share a PBIX file I would just need a little time to create an anonymised data set as the true project isn't homes addresses and cinemas. 

 

Any suggestions in the meantime though I'm happy to try out will help me no end if we can get this working. 

 

Thanks, 

 

Josh

Super User
Super User

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

@dedelman_clng

I see,  but the initial code had only one MINX( ). That's why I was asking, in case I was missing something

 

the bat signal? Smiley Very Happy

 

dedelman_clng New Contributor
New Contributor

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

@AlB- sorry, by "original" I meant in the 5th post Smiley Happy. It was at that point I started playing with the code.

 

Bat signal - I was going to @ some of the heavy hitters on these boards to come to the rescue Smiley Very Happy

Super User
Super User

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

@dedelman_clng

Ah ok, Then the question is for @Joshua_Peter: why the two MINXs( ) as opposed to only one MINX() you used initially?

If you have just a sample of the table with the important columns and a  few rows including Latitude and Longitude I can probably give it a go with that.

 

dedelman_clng New Contributor
New Contributor

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

@Joshua_PeterI think you undid more than you mean to.  The top of your code now has this 

 

VAR HouseLatitude = MIN('Route Information'[Lattitude])
VAR HouseLongitude = MIN('Route Information'[Longitude])
VAR EarthCircumference = 3959
VAR P =
DIVIDE ( PI (), 180 )
VAR P2 =
DIVIDE ( PI (), 360 )
VAR _ThisHouse = MIN('Route Information'[MEMNO])

VAR ClostestHouseLatitude = MIN('Route Information'[Lattitude])
VAR ClostestHouseLongitude = MIN('Route Information'[Longitude])

 

Notice the bolded sections - you are storing the same thing in ClosestHouse and House. That is why you are now getting zeroes.

 

Getting back to column vs measure, a calculated column allows you more freedom in some ways than a measure, because you do not have to put in "meaningless" aggregators (when you intend the measure to be used on a simgle record).  Now, a calculated column will also make data refreshes longer, because that is when the calculation happens (it killed my my computer trying to add the code as a column to the existing dataset, which may be from the nested MINX calls as @AlB mentioned).  I can definitely see why you would want the nearest neighbor as a column, since it shouldn't really be a dynamic value. 

Super User
Super User

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

@dedelman_clng

Yeah the nested MINXs could certainly increase execution time, potentially by a lot.

@Joshua_Peter

Since you said the very first version (with he cinemas) worked, I've modified that to take a first shot:

 

Distance to Closest House (Miles) =
VAR HouseLatitude = 'Route Information'[Lattitude]
VAR HouseLongitude = 'Route Information'[Longitude]
VAR EarthCircumference = 3959
VAR P =  DIVIDE (PI (), 180 )
RETURN
    MINX (
        'Route Information',
        VAR CinemaLatitude = 'Route Information'[Lattitude]
        VAR CinemaLongitude = 'Route Information'[Longitude]
        VAR _DistanceFromCurrent =
            ACOS (
                SIN ( HouseLatitude * P ) * SIN ( CinemaLatitude * P ) + COS ( HouseLatitude * P ) * COS ( CinemaLatitude * P ) * COS ( ( CinemaLongitude * P ) - ( HouseLongitude * P ) )
            ) * EarthCircumference
        RETURN
            IF (
                _DistanceFromCurrent <> 0,
                _DistanceFromCurrent
            )
    )

The only thing I've done is check whether the distance is zero (distance to the house itself) and if so return a blank(). The MINX( ) will ignore blanks so with that we'd obtain the minimum non-zero distance. This assumes the Latitude/Longitude pairs are unique in your data (and thus the distance to a house is only zero from the house itself). 

I haven't tested. It's just based on the info on your first posting. Try it out and let me know.

 

Cheers 

 

 

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

Morning @AlB & @dedelman_clng

 

Tried the formula you mentioned @AlB and currently its returning the below error; 

"An argument of function 'ACOS' has the wrong data type or the result is too large or too small."

 

The data types haven't changed though and it was returning in other formulas for the same sample size. 

 

I wrapped a MIN() round the first variable like @dedelman_clng did in his example and we are seeing some answers again but they aren't accuarte from a quick validation check. 

 

Distance to Closest House (Miles) =
VAR HouseLatitude = MIN('Route Information'[Lattitude])
VAR HouseLongitude = MIN('Route Information'[Longitude])
VAR EarthCircumference = 3959
VAR P =  DIVIDE (PI (), 180 )
RETURN
    MINX (
        'Route Information',
        VAR CinemaLatitude = 'Route Information'[Lattitude]
        VAR CinemaLongitude = 'Route Information'[Longitude]

I then wrapped MIN round both and even with the IF statement for 0 values; 

 

IF (
                _DistanceFromCurrent <> 0,
                _DistanceFromCurrent
            )

The entire column just returns a blank? 

 

Is there a way to validate the longitude/latitude coordinates aren't the same before the calculation is preformed? 

 

I will start preparing some data in a pbix now and attached asap. 

 

Thanks, 

 

Josh

 

 

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

Hi @AlB & @dedelman_clng

Please find attached link to the sample pbix.

 

I have included the calc for the closest cinema too so its as is and also the current/ latest formula for closest neighbour. 

 

The file is large due to the latitude / longitude lookup and supporting csv but if that causes an issue let me know and i can chop it up and remove that csv datasource.

 

Thanks, 

 

Josh

 

 

 

Super User
Super User

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

@Joshua_Peter

 

I can't download the file, don't know what is going on.

How big is it? Could you upload it to for instance tinyupload.com and I'll try from there? Or www.filedropper.com if it's larger than 50 MB

dedelman_clng New Contributor
New Contributor

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

I can't get to a file that large either - a dataset with 100 points in it should be sufficient if you can make that happen.

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,659)