I was able to calculate dynamically distnace between two points thanks using the following DAX, thanks to a post on Radacad Blog
Kilometers = var Lat1 = MIN('From City'[lat]) var Lng1 = MIN('From City'[lng]) var Lat2 = MIN('To Cities'[lat]) var Lng2 = MIN('To Cities'[lng]) ---- Algorithm here ----- 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 need to calculate the number of suppliers within X Km of a suburb, I have the lat & long of all the suburbs and suppliers.
Struggling to get something to work... any assistance with this would be greatly appreciated...
Solved! Go to Solution.
Hi @DamienW
Here's a mock-up of how I might do it.
Create a measure Suburb Supplier Distance that computes the distance between a single Supplier & Suburb (same formula as you had):
Suburb Supplier Distance = IF ( AND ( HASONEVALUE ( Supplier[Supplier] ), HASONEVALUE ( Suburb[Suburb] ) ), VAR Lat1 = SELECTEDVALUE ( Supplier[Latitude] ) VAR Lng1 = SELECTEDVALUE ( Supplier[Longitude] ) VAR Lat2 = SELECTEDVALUE ( Suburb[Latitude] ) VAR Lng2 = SELECTEDVALUE ( Suburb[Longitude] ) 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 )
Number of Suppliers within Selected Distance of Suburb = VAR MinDistance = MIN ( Distance[Distance] ) VAR MaxDistance = MAX ( Distance[Distance] ) RETURN COUNTROWS ( FILTER ( Supplier, [Suburb Supplier Distance] >= MinDistance && [Suburb Supplier Distance] <= MaxDistance ) )This is written so that you can have both lower and upper bounds on the distance, but you may want to rewrite with just an upper bound on the distance.
Well that's how I would do it. It may have to be adapted depending how your tables are structured.
Regards,
Owen
Proud to be a Datanaut!
Hi @DamienW
Here's a mock-up of how I might do it.
Create a measure Suburb Supplier Distance that computes the distance between a single Supplier & Suburb (same formula as you had):
Suburb Supplier Distance = IF ( AND ( HASONEVALUE ( Supplier[Supplier] ), HASONEVALUE ( Suburb[Suburb] ) ), VAR Lat1 = SELECTEDVALUE ( Supplier[Latitude] ) VAR Lng1 = SELECTEDVALUE ( Supplier[Longitude] ) VAR Lat2 = SELECTEDVALUE ( Suburb[Latitude] ) VAR Lng2 = SELECTEDVALUE ( Suburb[Longitude] ) 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 )
Number of Suppliers within Selected Distance of Suburb = VAR MinDistance = MIN ( Distance[Distance] ) VAR MaxDistance = MAX ( Distance[Distance] ) RETURN COUNTROWS ( FILTER ( Supplier, [Suburb Supplier Distance] >= MinDistance && [Suburb Supplier Distance] <= MaxDistance ) )This is written so that you can have both lower and upper bounds on the distance, but you may want to rewrite with just an upper bound on the distance.
Well that's how I would do it. It may have to be adapted depending how your tables are structured.
Regards,
Owen
Proud to be a Datanaut!
Thanks, worked a treat, ended up ditching the distance parameter table, and just created three measures with the distances that i wanted...
E.g
Suppliers in 5km = COUNTROWS (
FILTER (
Supplier,
[Kilometers] <= 5))
@Waynesaaiman could you share detail of your tables or a PBIX with your current data model, and how you want the report to behave?
I'm assuming you want certain visuals to be filtered to the closest supplier to another selected location?
We should be able to do this with some sort of measure that filters suppliers down to just the closest one.
Proud to be a Datanaut!
Hi,
I have the same scenario in my work, i have calculated distance as sugeasted but along with this i need to show supplier along with all the suburbs in a particular distance from it in one world map.
can anyone please guide me . how can i achieve it .
Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.
October was a busy month in the community. Read the recap article to learn about some of the events and content.
Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.
User | Count |
---|---|
69 | |
64 | |
58 | |
55 | |
54 |