Finding Minimum Haversine Distance from two sets of locations.

[Khurram_Ahmed]

I have a data for retailers' (A) and contractors' (B) locations (in seperate tables) and I want to calculate the haversine distances for all  AB data pairs and find the closest retailer for each contractor. I need to create a measure to achieve this as my data is in live mode. I am new to DAX and I am totally stuck on how to go about this. Reason for not putting the data is because of confidentiality issues. Any help is appreciated.

[v-easonf-msft · Accepted solution]

Hi, @Khurram_Ahmed 

This may involve mathematical functions provided by DAX.

Math and Trig functions 

You can also check if below thread could help.

Dynamic distances in Power BI 

Kilometers =
var Lat1 = MIN('From City'[lat])
var Lng1 = MIN('From City'[lng])

var Lat2 = MIN('To Cities'[lat])
var Lng2 = MIN('To Cities'[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

Best Regards,
Community Support Team _ Eason