So, I have these tables:
THPayments : [id, SK_DATE, amount, reference, currency_id]
DimCurrencies: [id, name ]
DimCurrenciesRates: [id,currency_id,SK_DATE,conversion_date]
THPayments[currency_id] is related to DimCurrencies[id]
DimCurrenciesRates[currency_id] is related to DimCurrencies[id]
I want to get the Conversion_rate for the Date of my payment with the actual currency id.
Explained in SQL woulb be like:
SELECT conversion_rate FROM DimCurrenciesRates
WHERE
conversion_rate.SK_DATE = THPayments.SK_DATE
and
conversion_rate.currency_id = THPayments.currency_id
The best I could do was, on the THPayments table, create a calculated column with:
=FILTER(DimCurrenciesRates;DimCurrenciesRates[SK_DATE] =[SK_DATE] && [currency_id] = DimCurrenciesRates[currency_id])
This effectibly returns the row i want, but, its a full row, i just need the "conversion_rate" value. Of course the error is:
"The expression refers to multiple columns. multiple columns cannot be converted to a scalar value"
How could I do this? Thanks!
