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
