I have 'Amount' Measure related to 'Location from', 'Location to' and 'Date' Dimensions, and I want to "look up" corresponding distance between 2 locations and multiply Amount by Km using Advanced Formula script. Distances are time-independent and input-ready, so I must store them in a planning model.

If I use simple DATA([d/Measures] = "Amount*Km") = RESULTLOOKUP([d/Measures] = "Amount")*RESULTLOOKUP([d/Measures] = "Km", [d/Date]="") it doesn't return anything, as it seems it is required to directly filter all dimensions in the 2nd RESULTLOOKUP expression to make it work.

If I use foreach on 'Location from', 'Location to' combination + if condition it doesn't work as I can't refer to a dimension member in a loop due to integer/float variables allowed only

If I store 'Location from', 'Location to' and 'Km' in a separate model and use LINK to link this model (also I need to keep the same Date Granularity in a linked Model for this), system tries generate all combinations for all not filtered dimensions members in DATA expression if I do

DATA([d/Measures] = "Amount*Km") = RESULTLOOKUP([d/Measures] = "Amount")*LINK([Linked_Model] , [d/Measures] = "Km", [d/Version] = "public.Actual", [d/Date]="")

So it either returns incorrect results or simply fails with an error that there are too many combinations...

What would be an appropriate approach to perform such simple "joins" of data subsets in SAC advanced formulas?