Forum Discussion

Jays's avatar
Jays
Frequent Visitor
2 years ago

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

Need Help
Tips and Tricks

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.