Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
GOT IT!
I just had to SUMMARIZE the FILTER result:
=SUMMARIZE(
FILTER(DimCurrenciesRates;DimCurrenciesRates[SK_DATE] =[SK_DATE] && [currency_id] = DimCurrenciesRates[currency_id]);
DimCurrenciesRates[conversion_rate] )
Thanks!
GOT IT!
I just had to SUMMARIZE the FILTER result:
=SUMMARIZE(
FILTER(DimCurrenciesRates;DimCurrenciesRates[SK_DATE] =[SK_DATE] && [currency_id] = DimCurrenciesRates[currency_id]);
DimCurrenciesRates[conversion_rate] )
Thanks!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
96 | |
76 | |
63 | |
55 |
User | Count |
---|---|
142 | |
107 | |
89 | |
84 | |
65 |