cancel
Showing results for
Did you mean:
Frequent Visitor

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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support

Hi @kbandito ,

Check the formula.

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

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution to help the other members find it.
7 REPLIES 7
Super User IV
Frequent Visitor

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.

Super User IV

@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

Proud to be a Super User!

Frequent Visitor

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

Super User IV

@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

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Super User IV

@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 __x1 = MAX('Table'[X])
VAR __y1 = MAX('Table'[Y])
VAR __id = MAX('Table'[ID])
RETURN

Also this version:

``````Near 2 =
VAR __x1 = MAX('Table'[X])
VAR __y1 = MAX('Table'[Y])
VAR __id = MAX('Table'[ID])
RETURN
COUNTROWS(__Table)``````

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

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Community Support

Hi @kbandito ,

Check the formula.

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

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution to help the other members find it.

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors