cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## Find the nearest location from two tables.

I have two tables one is the officer table and the second one is the Incident table both tables have latitude and longitude columns.
I want to plot both locations on the map. If I select any officer, on a map it should plot all the nearest locations within a given radius along with the selected officer's location.
So for that I merged these two tables because we can not plot the locations from two tables.  But now I am facing the issue with the count of incidents. because it also calculates the officer's locations. that I don't want. Only one location of the selected officer and the rest of the locations will be of Incidents. Further, I need to show by time range also, which means 10 mins before the officer's nearest location.
Can anyone help me with that?
I create an if parameter for the distance range:

Distance(KM) = GENERATESERIES(1, 50, 1)

Distance(KM) Value = SELECTEDVALUE('Distance(KM)'[Distance(KM)])

I find the closest distance by this measure:

Distance (KM) =
var lat1=MIN(officers1[Latitude])
var lng1= MIN(officers1[Longitude])

var lat2 =MIN('cases'[Latitude])
var lng2 = MIN('cases'[Longitude])

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

and then I calculate the count by this measure
Map Point Size =
IF([Distance (KM)] <= [Maximum Distance Km Value],
IF(HASONEFILTER('officers1'[Officer_Name]),
SWITCH(TRUE(),
[Distance Value] = 0,0.5,
0.25
),
1
),0)

Any help would be greatly appreciated.

Many thanks,

Jays

Community Support

Hi @Jays ,

Can you give me the pbix file for a better solution?

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors