Find top n closest locations based on lng and lat

[Krcmajster]

Hi All, 

Based on lng and lat I want to find top 10 closest locations when using drillthrough on specific cost center. Any ideas?

[Anonymous]

Hi @Krcmajster 

Maybe you can do something like below:

Distance = 
VAR __cc = SELECTEDVALUE( dimCC[Site Name]; FIRSTNONBLANK( dimCC[Site Name]; 1))
VAR __siteLat = CALCULATE( MAX( dimCC[Lat]); dimCC[Site Name] = __cc)
VAR __siteLong = CALCULATE( MAX( dimCC[Lon]); dimCC[Site Name] = __cc)

RETURN
    // Distance in km
    ACOS( 
        COS( RADIANS( 90 - MAX( DimXX[lat])))*
        COS( RADIANS( 90 - __siteLat)) + 
        SIN( RADIANS( 90 - MAX( DimXX[lat])))* 
        SIN( RADIANS( 90 - __siteLat)) *
        COS( RADIANS( MAX( DimXX[long]) - __siteLong))
        )*6371 

And then create a TOPN measure:

top10 = 
TOPN(
    10;
    SUMMARIZE(
        DimXX;
        "Distance"; [Distance]
    );
    [Distance]; ASC
)

It is not the fastest measure if you have many observations, but I believe it will work. If you have a lot of observations you should consider moving the calculations to the queries.

If this answer helps then please mark it as the accepted solution. Kudos is also appreciated.

[Krcmajster]

@Anonymous Thanks

So in my case let's say it would look like this:

Distance =

   VAR __cc = SELECTEDVALUE( Property'[BuildingName], FIRSTNONBLANK(Property'[BuildingName], 1))

   VAR __siteLat = CALCULATE( MAX( Property'[Latitude]), Property'[BuildingName] = __cc)

   VAR __siteLong = CALCULATE( MAX(Property'[Longitude]), 'Property'[BuildingName] = __cc)

RETURN

// Distance in km

  ACOS(

  COS( RADIANS( 90 - MAX( Property'[Latitude])))*

  COS( RADIANS( 90 - __siteLat)) +

  SIN( RADIANS( 90 - MAX( 'Property_'[Latitude])))*

  SIN( RADIANS( 90 - __siteLat)) *

COS( RADIANS( MAX(Property'[Longitude]) - __siteLong))

)*6371

top10 =

  TOPN(

  10,

  SUMMARIZE(

  Property',

  "Distance", [Distance]

 ),

  [Distance],ASC

  )

Will the drill through affect the measures?

[Anonymous]

Hi @Krcmajster 

I'm not quite sure that it will and neither that the TOPN function works as it should. But it is just an idea which might be able to lead you in the right direction.