How do I add a virtual column that contains value from a column in another table based on a set of conditions?

[christopher_loo]

I want to add a virtual column ‘Zone’ in ‘Order’ table where the value is derived from ‘Zone’ column in a reference table ‘Allocation’; based on the condition that the ‘Postal code’ fits the postal code range in the reference table. E.g.
Order
Address
Postal Code
Zone <<< virtual column

Allocation
Zone
Postal Code From
Postal Code To

What is the expression to use for the virtual column?

Thanks.

[GreenFlux · Accepted solution]

Hi @christopher.looi ,

See if this works:

SORT(SELECT(Allocation[Zone],AND(
    [Postal Code From]<=[_THISROW].[Zone],
    [Postal Code To]  >=[_THISROW].[Zone]

)))

If it doesn’t work, please post a picture of your column names/settings.

[christopher_loo]

@GreenFlux It works! Thanks!