Distance calculations - how many suppliers within X Kms

Hi DamienW

Here's a mock-up of how I might do it.

PBIX link here

1. I've assumed Supplier and Suburbs sit in two tables like this:

    

2. 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
   )

    

    

3. Create a Distance parameter table with a single column Distance, used to select the threshold distances.
4. Create this measure to count Suppliers within a selected distance of the selected Suburb:
   

   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