Issue with expression: List of Depots by the closest distance

[Daisy_Ramirez]

Hello, I may be overthinking this one. I have a Quote form where users can input their destination zip code and then select a Depot to make their purchase which is an Enum field. I’ve been struggling with the right expression for the list to display all depots sorted by the closest distance to the Destination

*(Destination I cross reference the zip code entered with a zip codes table to id the Geopoint - hence the look up.)
*[vir_depot_geopoint] is the geopoint of the Depots

Can someone assist with the expression? Any help would be outstanding - Thanks so much!

ORDERBY(
SELECT(Depot[Depot_ID],
DISTANCE(LOOKUP([Destination],“US Zip Codes”,“Zip”,“Geopoint”),[vir_depot_geopoint])<500),[Depot])

[MultiTech]

Hmmm… not sure about the ORDERBY part, but have you tried the select by itself? Does it return a list of depots like you want?

Try this for the select instead:

SELECT(Depot[Depot_ID],
DISTANCE(LOOKUP([_thisrow].[Destination],“US Zip Codes”,“Zip”,“Geopoint”), [vir_depot_geopoint]) < 500)
)
I added in [_thisrow] to specify which column should come from the table this formula lives in vs. a column in the table we’re doing a select on (aka, the Depot table).

[Daisy_Ramirez]

Thanks so much Steve and Multitech. No luck on the select statement. In order to build the list for a user to select from, I was thinking something like this might work as I simply need the Depot and the distance from the [Destination] to appear in the list for them to choose from:

LIST(Depot[Depot_ID],
DISTANCE(LOOKUP([_thisrow].[Destination],“US Zip Codes”,“Zip”,“Geopoint”),LOOKUP(Depot[Zip],“US Zip Codes”,“Zip”,“Geopoint”)))

Got this error: Cannot compare Text with list Depot[Zip]

[MultiTech]

You’ll need to provide a single value in that space

[Bellave_Jayaram]

Looks like you need to create a VC that pre-computes the DISTANCE between Destination and EVERY Depot which seems unnecessary as you are interested only in the nearest but I don’t think there is any other way to do what you are looking for unless you use sheet formulas or scripting to build your list for likely Destination ZIP codes.

[Daisy_Ramirez]

Hi Bellave, I would be fine with the VC that pre-computes the distance between Destination and EVERY depot as I can sort it by the distance. Would the VC reside in the Depot’s table or the Quote table? What would the formula look like for that VC? Thanks Bellave

[MultiTech]

The VC would need to be in the Depot table, and the formula would look something like this:

DISTANCE(HERE(), [vir_depot_geopoint])

Then your SELECT() formula to gather all the rows would be much simpler:

TOP(ORDERBY(Depot[Depot_ID], [Active_Distance]), 20)

Where [Active_Distance] is the new VC.

[Daisy_Ramirez]

Thanks so much but it seems DISTANCE (HERE(),[vir_depot_geopoint]) would be capturing the distance of the actual entry point and the depot in the Depot table. I’m needing for the distance to be calculated at the Quote level when the user inputs the zip code they’re in. I can capture the geopoint at the Depot table but will need the Depot listing (accessed by in the quote by dropdown) to display the number of km/miles from the zip code the user entered in the Quote.

So much thanks for the help - I feel like we’re close

[Bellave_Jayaram]

I think then you would need a third helper table which is a lookup table of distances between the geo point of each zip code and all other zip codes.

[Steve]

Your expression, reformatted for my clarity and with @MultiTech_Visions’ good suggestion to dereference _THISROW:

ORDERBY(
  SELECT(
    Depot[Depot_ID],
    (
      DISTANCE(
        LOOKUP(
          [_THISROW].[Destination],
          “US Zip Codes”,
          “Zip”,
          “Geopoint”
        ),
        [vir_depot_geopoint]
      )
      < 500
    )
  ),
  [Depot]
)

Your expression uses SELECT() to gather all Depot rows with a distance less than 500 km from the destination ZIP code, then orders those rows by whatever the Depot column of the Depot table contains, in ascending (A-Z) order.

This will get you the 20 closest depots:

TOP(
  ORDERBY(
    Depot[Depot_ID],
    DISTANCE(
      LOOKUP(
        [_THISROW].[Destination],
        “US Zip Codes”,
        “Zip”,
        “Geopoint”
      ),
      [vir_depot_geopoint]
    )
  ),
  20
)

[MultiTech]

Epic

[MultiTech]

@Daisy_Ramirez I think the solution is a hybrid of what we’ve got here:

In the Depot table, you need a VC that calculates the distance from the zip entered in the quote; but to find this zip code you need a way to isolate out the quote from all the rest, this way you can use a lookup to find the zip code’s geopoint and calculate a distance from that.

Isolating out the individual quote can be accomplished with a slice, you’ll need to create a condition formula that singles out the quote the user of the app is currently dealing with.

Once you have this slice, you can use a lookup to find the geopoint and then get the distance.

LOOKUP(any(Active_Quote[Destination]),“US Zip Codes”,“Zip”,“Geopoint”)

Insert this into the DISTANCE() formula on the depot table; this field will then hold the distance between the active quote’s destination and the geopoint of the depot.

When you do you’re select() to get the top 20, each depot has a filed that’s calculated the distance between it and the zip geopoint indicated in the active quote - using this you should get your top 20.

[Steve]

TOP(
  ORDERBY(
    Depot[Depot_ID],
    DISTANCE(
      LOOKUP(
        ANY(Active_Quote[Destination]),
        “US Zip Codes”,
        “Zip”,
        “Geopoint”
      ),
      [vir_depot_geopoint]
    )
  ),
  20
)