cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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_NameLatLong
AAA15.121555454.415845
BBB15.3544555445.48545
CCC25.451556454.545

 

POI

Area_NameLatLongBrandCategory
AAA15.121555454.415845LVLux
AAA15.121555454.415845PradaLux
BBB15.3544555445.48545xxxF&B
BBB15.3544555445.48545yyyF&B
CCC25.451556454.545zzzKids
CCC25.451556454.545zzzKids

 

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Hi @kbandito ,

 

Check the formula.

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

5.PNG

6.PNG 

 

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.

View solution in original post

7 REPLIES 7
Super User IV
Super User IV

@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.

@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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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

@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


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@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


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Community Support
Community Support

Hi @kbandito ,

 

Check the formula.

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

5.PNG

6.PNG 

 

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.

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

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