Forum Discussion

ainsleybilton's avatar
ainsleybilton
Regular Visitor
7 years ago
Solved

Find locations within a given distance

Hi All, this is my first post, so please be gentle with me.   I have a requirement to find locations within a fixed distance from a provided location.   My data model is as follows:     ...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    hi, ainsleybilton 

    You could try this way:

    Step1:

    Create a duplicate locations table as targe locations table.

    and in the targe locations table, add a Site Name column
    target locations = locations
    target site name = LOOKUPVALUE(Sites[Site Name],Sites[ID],'target locations'[ID])
    Step2:
    Create the relationship as below:
    Note: be careful the red marker.
    Step3:
    Use What if parameter to create a 'Distance' value
    Step4:
    Create a distance betweem two city measure
    Kilometers = 
var Lat1 = MIN('locations'[lat])
var Lng1 = MIN('locations'[lng])

var Lat2 = MIN('target locations'[lat])
var Lng2 = MIN('target locations'[lng])
---- Algorithm here -----
var P = DIVIDE( PI(), 180 )
var A = 0.5 - COS((Lat2-Lat1) * p)/2 + COS(Lat1 * p) * COS(lat2 * P) * (1-COS((Lng2- Lng1) * p))/2
var final = 12742 * ASIN((SQRT(A))) 
return final
    Step5:
    Then use 'Distance' value (parameter at the moment) to find employees within the specified distance of the site
    Amount = IF([Kilometers]<=[Distance Value],CALCULATE(COUNTA(employees[Employee Name]),ALL(Sites)))
    Step6:
    Drag target site name field from targe locations table and these two measure in a table visual, use Site Name from Sites table as Select a 'Site' filter value.
    Result:
    and this is a similar blog for you refer to:
    Here is a sample pbix file, please try it.
     
    Best Regards,
    Lin
     
     
v-lili6-msft's avatar
v-lili6-msft
Icon for Community Support rankCommunity Support
7 years ago

hi, ainsleybilton 

You could try this way:

Step1:

Create a duplicate locations table as targe locations table.

and in the targe locations table, add a Site Name column
target locations = locations
target site name = LOOKUPVALUE(Sites[Site Name],Sites[ID],'target locations'[ID])
Step2:
Create the relationship as below:
Note: be careful the red marker.
Step3:
Use What if parameter to create a 'Distance' value
Step4:
Create a distance betweem two city measure
Kilometers = 
var Lat1 = MIN('locations'[lat])
var Lng1 = MIN('locations'[lng])

var Lat2 = MIN('target locations'[lat])
var Lng2 = MIN('target locations'[lng])
---- Algorithm here -----
var P = DIVIDE( PI(), 180 )
var A = 0.5 - COS((Lat2-Lat1) * p)/2 + COS(Lat1 * p) * COS(lat2 * P) * (1-COS((Lng2- Lng1) * p))/2
var final = 12742 * ASIN((SQRT(A))) 
return final
Step5:
Then use 'Distance' value (parameter at the moment) to find employees within the specified distance of the site
Amount = IF([Kilometers]<=[Distance Value],CALCULATE(COUNTA(employees[Employee Name]),ALL(Sites)))
Step6:
Drag target site name field from targe locations table and these two measure in a table visual, use Site Name from Sites table as Select a 'Site' filter value.
Result:
and this is a similar blog for you refer to:
Here is a sample pbix file, please try it.
 
Best Regards,
Lin
 
 
  • ainsleybilton's avatar
    ainsleybilton
    Regular Visitor
    7 years ago

    v-lili6-msftthanks for your help. You are a star, this worked a treat.

     

    :)

  • OzMartijn's avatar
    OzMartijn
    Frequent Visitor
    2 years ago

    Hey v-lili6-msft , sorry to comment on something that's a bit old... do you know if there's limits to the amount of data this can process? I am looking at 18M addresses, and whilst following this pretty closely, i keep hitting the available resources problem.