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 f...
Need Help
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 ?

 

 

Eric_Zhang's avatar
Eric_Zhang
Microsoft Employee
9 years ago
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's avatar
    Phil_Seamark
    Microsoft Employee
    9 years ago

    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's avatar
      Aeloff
      Regular Visitor
      8 years ago

      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