Forum Discussion

HenryJS's avatar
HenryJS
Post Prodigy
6 years ago

Calculate Radius Distance With Dax

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 advi...
Data Protection
Need Help
Show and Tell
Tips and Tricks
v-juanli-msft's avatar
v-juanli-msft
Community Support
6 years ago

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's avatar
    HenryJS
    Post Prodigy
    6 years ago

    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