Forum Discussion

Laraib_Ghafoor's avatar
Laraib_Ghafoor
Regular Visitor
9 years ago

Distance between 2 locations

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.

 

 

Need Help

6 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    9 years ago

    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's avatar
      Laraib_Ghafoor
      Regular Visitor
      9 years ago

      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 ?

       

       

      • Phil_Seamark's avatar
        Phil_Seamark
        Microsoft Employee
        9 years ago

        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.