Calculate Radius Distance With Dax

[HenryJS]

Hi all,

I have two tables (below) and would like to create a 25km distance around the 'Company Name' and find which of the 'CandidateRef' in the table beneath are within this.

Can anyone advise how to do this?

1. Company Table

3. Candidate Table

Thanks,

Henry

[v-juanli-msft]

Hi @HenryJS 

If it is difficult to implement, please feel free to let me know.

According to your previous post, it seems you want to get distances of every 'CandidateRef' away from every 'Company Name' ,

then filter out the Candidates for every 'Company Name' which distances are less than or equal to 25km distance.

In this case, i would suggest you to exact data from two tables about location name,

(add a column to define the type "Company' or 'Candidate')

and latitude/longitude, then append two tables.

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[HenryJS]

Hi @v-juanli-msft ,

Yes - my understanding is that I need to calculate the distance of every company from every candidate in the 'CandPost' table.

However when I created a new column and input the below DAX in I got the same value for every candidate? Please see below column 'Distance from Compound'.

Do I need a column for every company?

Distance From Compound =

var Lat1 = MIn('CandPost'[Latitude])

var Lng1 = MIN('CandPost'[Longitude])

var Lat2 = MIN('CompPost (2)'[Latitude])

var Lng2 = MIN('CompPost (2)'[Longitude])

---- 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

[v-juanli-msft]

Hi @HenryJS 

Please refer to : 

Display Points within a Distance Radius on a Power BI Map

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.