Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DamienW
Frequent Visitor

Distance calculations - how many suppliers within X Kms

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

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

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:image.png

     

  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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

Great post...I was wondering if you would know how to convert this to miles instead of km? Also, the number in VAR final = '12742', what is the number referencing to? Thanks again. 

Anonymous
Not applicable

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 .

Waynesaaiman
New Member

Hi @OwenAuger, thank you for your solution. I was wondering if you can assist me further. I have a similar scenario however my distances have been calculated via the Google API as a function. How would I then dynamically be able to see the closest supplier. Thanks.

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
OwenAuger
Super User
Super User

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:image.png

     

  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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.