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

Anonymous

I think my approach solves the issue of eliminating the zero distance to the the base house itself. The problem lies with the distance calculation formula. I did a test. Instead of using the haversine formula, which is raising the error, I just used a simple approximation based on the Euclidean distance between two points and adjusting for the approximate size in km of a degree of Lat/Lon. A rough approximation probably but that is beside the point. I just wanted to see if the approach works. It seems to. Here's the calculated column:

Distance to Closest House (Miles)3 = 
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 _DistanceFromCurrentRough = 80*SQRT(POWER((HouseLatitude-CinemaLatitude);2) + POWER((HouseLongitude-CinemaLongitude);2))
        RETURN
            IF (
                _DistanceFromCurrentRough <> 0;
                _DistanceFromCurrentRough
            )
    )

This provides results that look meaningful.

On top of that, I tried a variation that does not exclude the zero distances obtained:

Distance to Closest House (Miles)3 = 
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 _DistanceFromCurrentRough = 80*SQRT(POWER((HouseLatitude-CinemaLatitude);2) + POWER((HouseLongitude-CinemaLongitude);2))
        RETURN
                _DistanceFromCurrentRough
            
    )

and this, as expected, yields zero in all rows due to the comparison with the base house itself which was the initial problem/question.

So that can be solved with this approach.

Now the next issue regards something in the haversine formula or in the data or in some weird behavior that raises the error of ACOS mentioned earlier. But that's a whole different story.

Agree?

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?

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

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

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? :smileyvery-happy:

AlB- sorry, by "original" I meant in the 5th post :smileyhappy:. 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 :smileyvery-happy:

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.

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. 

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 

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

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

Anonymous

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

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.

dedelman_clng, Anonymous

agree. A short data sample would be more convenient for quick testing

http://www.filedropper.com/closestdestinationsample 

Its 97mb because of the latitude and longitude table to pair with the postcodes. 

I never started with the long/lat coordinates i had to get them from another source and then tie them together based on postcode. 

If thats still an issue, i can take a static copy of the tables with all the values and pass it over that way, should make it alot smaller without the long/lat csv included. 

Thanks, 

Josh

Give me 20 minutes and ill have the smaller file size. 

Thanks, 

Josh

Hi AlB dedelman_clng, 

This link should work now think its 60kb now. 

Available here.

The v2 file.  

Thanks, 

Josh

or here;

http://www.filedropper.com/closestdestinationsamplev2 

AlB dedelman_clng

Thinking onwards i may need to filter these results at a later point aswell based on a DATE field so ALL() might not be the best and//or using a measure might be better. 

So with the date column we could say on 22/01 if there anyone traveling with a neighbor greater than 20 miles away. 

With the ALL() in the formual would that egnore the visual filter to only show house refferences travelling today as an exmaple? 

Or would the visual slicer just limited the results and base the calculation on the data thats relevant? 

Thanks, 

Josh

If the nearest point is to be dynamic, it will have to be a measure. You would still want to use ALL, but then would have to put filters back onto the data. I'll take that into consideration when I start looking at your sample file.

Anonymous, dedelman_clng

I'm having a look at the file and with my approach I do get that error that Anonymous was mentioning:

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

That's a bit weird. I guess the type is not the problem here and ACOS should throw that error if the argument is outside [-1,1] which is the valid input range for the function. I checked the values that are passed to ACOS and all fall within that range. There are some 1s so I thought it might be that due to rounding those ones might be actually 1+1e-N with N=10 or greater. I've checked up to the 15th decimal and they are all zeros. Plus from a couple of tests I've run ACOS ignores the decimals further than the 15th.

So I'm a bit puzzled as to why this error is generated. I think the approach should work otherwise

AlB dedelman_clng

If we had a duplicate Route Information table, or a distinct values only table taking it back slightly and to emulate the working scenario with the Cinema, is there a way to filter the duplicate table to remove the same house coordinates before the formula is ran based on the MEMNO? 

Would that work, then the answer cant be 0 as the address would be removed as an option per MEMNO? 

Then just execute the same formula as the cinema one? 

Thanks, 

Josh

Hi AlB

Yeah i have tried the Calculated column on the full data set and prior to any slicers been applied have managed to verify the distances produced are very close to accurate, 100% accurate enough for what i intend. 

Slight issue when we filter the data though, so there is a Date column which will date when these points are relevant or occured over the previous 7 days. 

If i filter to only show houses on the 16th January as an example the closest distance remains the same but its closest may be from a house on the 18th January so in this scanario wouldn't be a possible closest. 

Based on the conversation i did try creating a caluclated measure with the same formula without much sucsess it doesn't relate to the MEMNO so wont display the values for the closest distance in a table view using the MEMNO. 

So it is working from a total dataset view but the information wont update as we filter down for a specific date or only houses with a closest cinema of Doncaster Vue as an example. 

How would be make the same working formula work with filtered data, as a measure? 

Thanks though from a dataset point of view its great and from what points ive validated is fairly accurate. 

Thanks, 

Josh

Anonymous

Hmmm... I'm a bit surprised it's accurate as I just put it together quite quickly and only with rough numbers. I mean I wouldn't trust my formula if I were you :smileyvery-happy: Anyway we can keep using it until all other issues are solved and then we could try to change it if necessary as that wouldn't affect the rest.

I was going to post the measure but I see dedelman_clng has already done it. 

Anonymous

What are you going to need regarding the dates? Filter only a single day? A period?

dedelman_clng

Out of curiosity, what problems did you have with ALLEXCEPT and KEEPFILTERS ?