cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pstanek
Post Patron
Post Patron

Range

I Have two tables  with columns

1) Office  Latitude Longitude

2)Client Latitude Longitude

 

I need to know all clients who are less than 30 kilometers from certain office.

Can I calculate it?

 

3 REPLIES 3
sdjensen
Solution Sage
Solution Sage

I tried making a simple google search and found this page. I didn't read it in details but it sounds like something you could use.

 

This page have something similar.

/sdjensen

Main problem is that I have two tables in power bi without exact relation except for same parameters - latitude, longitude.

@pstanek

 

In this scenario, since you have two tables without any relationship, we can first merge these two tables and then do the distance calculation with M language in Query Editor. The link provided by @sdjensenis very useful.

I assume the two tables are like below. Please refer to following steps.
1.png

 

  1. In Query Editor, add 2 custom columns for each table. Make sure the Data Type is ‘Decimal Number’.
    Lat1_Rad = ([Latitude_1] / 180) * Number.PI
    Lon1_Rad = ([Longitude_1] / 180) * Number.PI
    Lat2_Rad = ([Latitude_2] / 180) * Number.PI
    Lon2_Rad = ([Longitude_2] / 180) * Number.PI
    
    2.jpg
  2. Add index (1) column for both of Client and Office tables.
    3.png
  3. Select Office table and click ‘Merge Queries’. Select Index as the matching columns. Set ‘Join Kind’ as ‘Full Outer’.
    4.jpg
  4. Remove the Index columns and add a custom column in the new merged Office table.
    Distance_KM = Number.Acos(Number.Sin([Lat1_Rad]) * Number.Sin([Lat2_Rad]) + Number.Cos([Lat1_Rad]) * Number.Cos([Lat2_Rad]) * Number.Cos([Lon2_Rad]-[Lon1_Rad])) * 6371
    5.jpg
  5. Close and apply Query Editor, create a calculated column in Office table.
    Clients_LessThan30_KM_From_Office = 
    IF ( Office[Distance_KM] < 30, Office[Distance_KM], BLANK () )
    
  6. Drag Table chart into your canvas to show the expected results.
    6.png

Helpful resources

Announcements
Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Power BI Womens Summit 2021 768 x460.jpg

Interviews, learning sessions, allies, and more!

#PowerBIWomenSummit

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!