Hi @GBGAnalyst
I have looked at your question and the pbix file you share, highligted below are the next steps for you to take to get the result you wanted.
You Need to Create the following measures to ensure interactive and dynamic selection of the calculation.
Distance = MAX(‘From’[From]) & " to " & MAX(‘To’[To])
From Title = IF( HASONEFILTER(‘From’[From]), MAX(‘From’[From]),“No City Seleted” )
---- If you want your result as Kilometer distance----
Kilometers =
var Lat1 = MIN(‘From’[lat])
var Lng1 = MIN(‘From’[lng])
var Lat2 = MIN(‘To’[lat])
var Lng2 = MIN(‘To’[lng])
---- Also assign this as variable -----
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
---- If you want your result as Meter distance----
Meters =
VAR cv = 1000
RETURN
[Kilometers] * cv
SO Now, when you have successfully created this measures, create the following filter column
State
Local government
From Title
Then create a matrix table for your calculation: here are the field you need to takes to your matrix table
To should be drag in to Row
Distance should be drag to Values
Meter should be drag to values after distance
If you want to show all this in map, follow the below procedure:
Pick MAP, from visualization then perform the following,
lat should be drag to Latitude
lng should be drag to Logitude
Distance should be drag to Tooltips
Meters should be drag to Tooltips
Lastly, if you want to Picked, for example a closest outlet which is close to a selected From in the filter table,
Then carryout the folowing filter exercise on the Matrix table and the Map separately by clicking:
FILTER PANEL
Navigate to where To is in the filter:
make Filter Type to be TOP N
Show Item to be Bottom then (if you want it to be 10 closest outlet then type 10)
drag Meter to the By Value
(Note: this is to be done to Matrix table and Map)
Then you are good to go, if you need further help on this don’t hesitate to share.
Please try this out and if it work, Kindly make it a Solution
DAVID
