cancel
Showing results for
Did you mean:
Anonymous
Not applicable

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

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

## 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?

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

Super User

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

@dedelman_clng

Ah ok, Then the question is for @Anonymous: 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.

New Contributor

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

@AnonymousI 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.

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

@Anonymous

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

Anonymous
Not applicable

## 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

Anonymous
Not applicable

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

Hi @AlB & @dedelman_clng

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

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

@Anonymous

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

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.

Announcements