Finding Minimum Haversine Distance from two sets of locations.

Khurram_Ahmed — Thu, 03 Nov 2022 14:40:03 GMT

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.

Re: Finding Minimum Haversine Distance from two sets of locations.

v-easonf-msft — Fri, 04 Nov 2022 06:04:07 GMT

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

topic Re: Finding Minimum Haversine Distance from two sets of locations. in DAX Commands and Tips

Finding Minimum Haversine Distance from two sets of locations.

Re: Finding Minimum Haversine Distance from two sets of locations.