Selectedvalue with dynamic distances (Lat / Long calculation is fine, selected value fuction issue)

[swatsonlord]

Hi there, 

I am trying to create a dynamic distance measure in Power BI so that when the user selects the Address or Site ID in the slicer, the table then filters to all sites around it within 100km. 

I have tried the below method: 

My base table is All Sites (this lists every site recorded, there can be multiple Site ID's per client, as below): 

Site ID: 10001    Client 1   Lat    Long 

Site ID: 10001    Client 2   Lat    Long 

etc

I have then created a table that groups this data so that there is 1 site per Site ID like below: 

Site ID: 10001   Lat    Long 

Site ID: 10002   Lat    Long 

etc

I have attempted the below formula to get the selected value, and then the distance per selected value: 

SelectedSiteLatitude = SELECTEDVALUE('All Sites Grouped'[Latitude], BLANK())

SelectedSiteLatitude = SELECTEDVALUE('All Sites Grouped'[Longitude], BLANK())

Distance: 

SV Distance Measure =

VAR Lat1 = RADIANS(SELECTEDVALUE('All Sites Grouped'[Latitude], BLANK()))  

VAR Lon1 = RADIANS(SELECTEDVALUE('All Sites Grouped'[Longitude], BLANK())) 

VAR Lat2 = RADIANS(CALCULATE(MAX('All Sites'[Latitude]),ALL('All Sites')))  

VAR Lon2 = RADIANS(CALCULATE(MAX('All Sites'[Longitude]),ALL('All Sites')))  

VAR dLat = Lat2 - Lat1

VAR dLon = Lon2 - Lon1

VAR A = SIN(dLat / 2) * SIN(dLat / 2) +

        COS(Lat1) * COS(Lat2) *

        SIN(dLon / 2) * SIN(dLon / 2)

VAR C = 2 * ATAN(SQRT(A) / SQRT(1 - A))

VAR Distance = 6371 * C  -- Earth's radius in kilometers

-- Filter to show only sites within 100 km of the selected site

RETURN IF(Distance <= 100, Distance, BLANK())

The problem I am getting is that the below part of the formula is just bringing back one value, instead of all of the values in the table and then comparing against the selected lat/long, so all the distance formula's are the same regardless (as if I had a static set of lat/longs). 

VAR Lat2 = RADIANS(CALCULATE(MAX('All Sites'[Latitude]),ALL('All Sites')))  

VAR Lon2 = RADIANS(CALCULATE(MAX('All Sites'[Longitude]),ALL('All Sites')))  

It is also filtering the table down so that only the selected value shows up, and when I use the edit interactions this then messes up the seleted value function. 

I have set the tables with a many (All Sites) to one (All Sites grouped) relationship. 

Any help would be massively appreciated. 

Thanks, 

[TomMartens]

Hey @swatsonlord ,

not sure what you want to achieve, consider creating a PBIX file that contains sample data but still reflects your semantic model (tables, relationships, calculated columns, and measures), and upload the model to OneDrive, Google Drive, or Dropbox and share the link. If you use a spreadsheet to create the sample data, share the spreadsheet as well.
Do not forget to explain the expected result based on sample data you provide.

You have to consider the following things:

• A calculated table will only be recalculated during data refresh, meaning a recalculating is not triggerd when a user selects a different slicer value
• A measure always will return a single value, not a table.

Regards,

Tom