Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
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" )
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 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 ?
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.
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?
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
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.