Customers and closest Store

DAX DAX Calculations
1

So, what I want to accomplish is to be able to tell what store a customer is near based on the customer’s address. There would be a table of customers with their addresses and then a table of stores with their addresses. I want to be able to find which store is the shortest distance to the customer’s address.

Is something like this possible in Power BI? I have not created anything yet because I am trying to figure out how this would work.

I did come across the article at https://radacad.com/dynamic-distances-in-power-bi. But the information is written for the distance between two locations. What if I need to run a hundred customer addresses against a table of 75 store locations to determine which is closest and reflect the distance in miles?

After I figure this out, phase 2 is to figure out which customers have not purchased specified products so that I can determine what needs to be stocked at a given store location.

2

Created a new thread in data mentor that should give some guidance around this

1 Like
3

I had a chance to revisit this following the example and I am striking out. See the attached image. It does not recognize the field names, even though I double-checked them.

I created a relationship between the two tables based on the street address but that wouldn’t be the reason its telling me the columns don’t exist and then there is the ATAN2 error.

2024-02-15_10-28-41
2024-02-15_10-28-412038×1267 338 KB

This was another approach I tried based on AI but it also failed.

2024-02-15_10-54-06
2024-02-15_10-54-062038×1267 334 KB

4

Below is the pure formula that Gemini gives based on what I asked it to do.

ClosestDistance =
VAR CurrentLocation = SELECTEDVALUE('YourTable'[LocationID])
VAR ClosestLocation =
    MINX(DISTINCT('YourTable'[LocationID]),
        VAR OtherLocation = EARLIER('YourTable'[LocationID])
        RETURN
            IF(
                OtherLocation <> CurrentLocation, // Exclude self-comparison
                69.172 * DEGREES(ASIN(
                    SQRT(
                        POWER(SIN(RADIANS([Lat_LocationCurrent]) - [Lat_LocationOther]) / 2), 2) +
                        COS(RADIANS([Lat_LocationCurrent])) * COS(RADIANS([Lat_LocationOther])) *
                        POWER(SIN(RADIANS([Lon_LocationCurrent] - [Lon_LocationOther]) / 2), 2)
                    )
                )),
                NULL // Exclude self-comparison
            )
    )
RETURN
    IF(ISBLANK(ClosestLocation), BLANK(), ClosestDistance)