Distance between 2 locations

[Laraib_Ghafoor]

Hi,

I have Location Code, Location Name, Longitude, Latitude, Revenue data in an excel. I have plotted those locations on the bubble map in Power BI. I want that when a user selects 2 locations from a slicer(or any other better option), then distance between those 2 locations is calculated and displayed on the report anywhere.

[Phil_Seamark]

Hi @Laraib_Ghafoor

I had some success using the following technique.  If you select two points from the map it will try and work out the distance (in KM) for you.

My data was as follows :

Column1	Lat	        Lng
A	-41.1296	175.12
B	-41.124	        175.1165
C	-41.1217	175.1088

I then created the following calculated measure on that table as follows

Kilometers Between = 
VAR Here = MAX('Table1'[Column1])
VAR There = MIN('Table1'[Column1])

VAR Lat1 = CALCULATE(MAX([Lat]),'Table1'[Column1]=Here)
VAR Lng1 = CALCULATE(MAX([Lng]),'Table1'[Column1]=Here)
VAR Lat2 = CALCULATE(MAX([Lat]),'Table1'[Column1]=There)
VAR Lng2 = CALCULATE(MAX([Lng]),'Table1'[Column1]=There)

var P = PI()/180
var a = 0.5 - COS((Lat2-Lat1) * p)/2 + COS(Lat1 * p) * COS(lat2 * P) * (1-COS((Lng2- Lng1) * p))/2
var KM = 12742 * ASIN((SQRT(a)))

RETURN IF(
		COUNT([Column1])=2,
		KM,
		"Please select 2 points"
		)

[Laraib_Ghafoor]

hi @Phil_Seamark, Thank for your response !!!

I have 30 region offices and around 2000 collection centers. I have made 2 slicers (one for region offices and one for collection centers). I want that when user selects a region from one slicer and a collection center from another, than calculated distance is displayed somewhere on the screen. How can this be done ?

My table looks like :-

Location Name     Region      Longitude   Latitude

Location1                Region1    12345           56789

Location2                Region1    12345           56789

Location3                Region2    12345           56789

Location4                Region2    12345           56789

How can i relate this new calculated measure with the slicers where i click the locations ?

[Eric_Zhang]

---

@Laraib_Ghafoor wrote:

hi @Phil_Seamark, Thank for your response !!!

I have 30 region offices and around 2000 collection centers. I have made 2 slicers (one for region offices and one for collection centers). I want that when user selects a region from one slicer and a collection center from another, than calculated distance is displayed somewhere on the screen. How can this be done ?

My table looks like :-

Location Name     Region      Longitude   Latitude

Location1                Region1    12345           56789

Location2                Region1    12345           56789

Location3                Region2    12345           56789

Location4                Region2    12345           56789

How can i relate this new calculated measure with the slicers where i click the locations ?

---

@Laraib_Ghafoor

I think you'll have to duplicate the table and use two slicers, one for table and the other for the duplicated table. Then you can use MAX/LASTNONBLANK to get the long/latitude from each table and calculate the distance with @Phil_Seamark's solution.

[Phil_Seamark]

HI @Laraib_Ghafoor,

In the sample data you PM'd to me, some regions have more than one Lat/Lng.  Which is your preferred Lat/Long to use for a Region A when there is more than one?

[Aeloff]

Hi Phil, can you please assist me with a variation on that distance inbetween solution above. I am tryring to find the distance in meters and between to sets of lat/lng co-ordinates.

I have the excel formula as

=SQRT(([@[Place_latitude]]-[@[Latitide]])^2 + ([@[Place_longitude]]-[@[Longitude]])^2)

Can you please help me to create this formula in power query as i have to much data to do it through excel and serverly stuck

[Phil_Seamark]

Hi @Laraib_Ghafoor

The algorithm I used is only based on direct lines and does not give you a distance based on road distance.

Otherwise, all it needs is a table of locations as you posted and it should work.

You are welcome to pm me some data if you want to keep the locations private and I can build into a PBIX file using the measure I previously posted.