Count of outlets within a 3-km distance from Selected Area.

[kbandito]

Hi, i have two tables here where my goal is to find out how many POIs/Brands are within 3-km distance from the selected Area_Name.

Area

Area_Name	Lat	Long
AAA	15.12155	5454.415845
BBB	15.354455	5445.48545
CCC	25.4515	56454.545

POI

Area_Name	Lat	Long	Brand	Category
AAA	15.12155	5454.415845	LV	Lux
AAA	15.12155	5454.415845	Prada	Lux
BBB	15.354455	5445.48545	xxx	F&B
BBB	15.354455	5445.48545	yyy	F&B
CCC	25.4515	56454.545	zzz	Kids
CCC	25.4515	56454.545	zzz	Kids

This measure has done the job in calculating distance between the Selected Area and all the POIs.

Spoiler

Distance from Selected Area:
VAR Lat2 = SELECTEDVALUE( 'Area'[Latitude] )

VAR Lng2 = SELECTEDVALUE( 'Area'[Longitude])

VAR Lat1 = calculate(MIN(POI[Latitude]),ALLEXCEPT('Area','Area'[Area Name]))

VAR Lng1 = calculate(MIN(POI[Longitude]),ALLEXCEPT('Area','Area'[Area Name]))

VAR P =

DIVIDE ( PI (), 180 )

VAR A =

0.5

- COS ( ( Lat2 - Lat1 ) * p )

/ 2

+ COS ( Lat1 * p )

* COS ( lat2 * P )

* (

1

- COS ( ( Lng2 - Lng1 ) * p )

)

/ 2

VAR final =

12742 * ASIN ( ( SQRT ( A ) ) )

RETURN

final

However, I am having problem in counting the number of POI within a 3-km distance using a second measure.

Your help is kindly appreciate.

[v-jayw-msft · Accepted solution]

Hi @kbandito ,

Check the formula.

Measure = CALCULATE(DISTINCTCOUNT(POI[Brand]),FILTER(POI,[Distance from Selected Area]<3000))

 

Best Regards,

Jay

[amitchandak]

@kbandito , This blog should help you do so

https://stackoverflow.com/questions/27928/calculate-distance-between-two-latitude-longitude-points-h...

https://community.powerbi.com/t5/Desktop/Distance-Calculation-in-Power-BI/td-p/206979

[kbandito]

@amitchandak 

Hi, i have already solved how to calculate distance. My problem is counting the number of outlets within a certain distance from the selected area.

[amitchandak]

@kbandito , This is what I think of.

One way is to cross join the first table with itself and have the distance calculated at the column level, we can find <=3 KM

Second is once a location is selected we use the measure to find the difference and use row context to filter location LT 3 KM

[kbandito]

@amitchandak 
I have tried that, but because I have a list of 2,500 areas, PBI crashed.

[Greg_Deckler]

@kbandito - What if you did something like Near, just posted it to the quick measures gallery. You should be able to adapt it pretty easily to your situation I think!!

Near 1 = 
  VAR __radius = 5
  VAR __x1 = MAX('Table'[X])
  VAR __y1 = MAX('Table'[Y])
  VAR __id = MAX('Table'[ID])
  VAR __Table = ADDCOLUMNS(FILTER(ALL('Table'),[X]<=__x1+__radius && [X]>=__x1-__radius && [Y]<=__y1+__radius && [Y]>=__y1-__radius),"Distance",SQRT( (__x1 - [X])^2 + (__y1 - [Y])^2) )
RETURN
  COUNTROWS(FILTER(__Table,[Distance]<=__radius))

Also this version:

Near 2 = 
VAR __radius = 5
VAR __x1 = MAX('Table'[X])
VAR __y1 = MAX('Table'[Y])
VAR __id = MAX('Table'[ID])
VAR __Table = FILTER(ALL('Table'),[X]<=__x1+__radius && [X]>=__x1-__radius && [Y]<=__y1+__radius && [Y]>=__y1-__radius)
RETURN
COUNTROWS(__Table)

https://community.powerbi.com/t5/Quick-Measures-Gallery/Near/m-p/1360099#M616

[Greg_Deckler]

@kbandito Well I was going to say that this looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.

But, if I understand you have 2500 that would be 2500!? Or am I wrong and that would just be 2500*2500 or over 6M? What I would do if it is 6M is to do the join in Power Query. That shouldn't be a problem. Basically join the column with itself and include the latitude and longitude for each pair. Then you could implement your distance in a column and everything would be done at data load without any measures and such. Curious, did you get the distance formula here? https://community.powerbi.com/t5/Quick-Measures-Gallery/Going-the-Distance/m-p/963267#M423