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 advise how to do this?

 

1. Company Table

 

 

3. Candidate Table

 

 

Thanks,

 

Henry

 

Data Protection
Need Help
Show and Tell
Tips and Tricks

3 Replies

  • 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