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
     
     
ainsleybilton's avatar
ainsleybilton
Regular Visitor
7 years ago

As an additional bit of information, I tried using a calculated table to get the distance between the locations as below:

 

DistanceFromSites =
SELECTCOLUMNS (
    ADDCOLUMNS (
        GENERATE (
            SELECTCOLUMNS (
                FILTER(Locations,Locations[Location Type]="Site Location"),
                "Site ID", 'Locations'[Location ID],
                "Latitude From", 'Locations'[Latitude],
                "Longitude From", 'Locations'[Longitude]
            ),
            SELECTCOLUMNS (
                FILTER(Locations,Locations[Location Type]="Employee Location"),
                "Employee ID", 'Locations'[Location ID],
                "Latitude To", 'Locations'[Latitude],
                "Longitude To", 'Locations'[Longitude]
            )
        ),
        "Distance",
        VAR Pie =
            DIVIDE ( PI (), 180 )
        VAR Arc =
            0.5
                - COS ( ( [Latitude To] - [Latitude From] ) * Pie )
                    / 2
                + COS ( [Latitude From] * Pie )
                    * COS ( [Latitude To] * Pie )
                    * (
                        1
- COS ( ( [Longitude To] - [Longitude From] ) * Pie )
                    )
                    / 2
        VAR KMDistance =
            12742 * ASIN ( SQRT ( Arc ) )
        RETURN
            KMDistance
    ),
    "Site", [Site ID],
    "Employee", [Employee ID],
    "Distance KM", [Distance]
)
 
The above resulted in 11M records in my table which broke my models ability to filter in the correct direction.
 
I would dearly like the solution to calculate the distance from the selected SITE only when the filter is applied and to do it against all of the rows within the Locations table. I really don't want to have to calculate the distance between all possible locations just to use the distance filter.
 
I really hope you guys can help.