Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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.
Solved! Go to Solution.
Hi @kbandito ,
Check the formula.
Measure = CALCULATE(DISTINCTCOUNT(POI[Brand]),FILTER(POI,[Distance from Selected Area]<3000))
Best Regards,
Jay
Hi @kbandito ,
Check the formula.
Measure = CALCULATE(DISTINCTCOUNT(POI[Brand]),FILTER(POI,[Distance from Selected Area]<3000))
Best Regards,
Jay
@kbandito , This blog should help you do so
https://community.powerbi.com/t5/Desktop/Distance-Calculation-in-Power-BI/td-p/206979
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
@amitchandak
I have tried that, but because I have a list of 2,500 areas, PBI crashed.
@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
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |