Find locations within a given distance

[ainsleybilton]

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:

I am trying to achieve the following :-

1. Select a 'Site' filter value
2. Specify a 'Distance' value (parameter at the moment)
3. Find employees within the specified distance of the site

Can anyone help with this please? I am really stuck.

[v-lili6-msft · Accepted solution]

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

https://docs.microsoft.com/en-us/power-bi/desktop-what-if

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:

https://radacad.com/dynamic-distances-in-power-bi

Here is a sample pbix file, please try it.

Best Regards,

Lin

[OzMartijn]

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.

[ainsleybilton]

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

🙂

[ainsleybilton]

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.