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.
I need to find out how to find the right result for the two column exchange rate and avg.rate based on the match of column "month" "year" and "currency"
Economic | change in DKK | |||||||||
amount | currency | month | year | currency | month | year | exchange rate | avg. rate | ||
10 | EUR | 1 | 2018 | EUR | 1 | 2018 | 1.5 | 1.3 | ||
20 | CZK | 2 | 2018 | CZK | 2 | 2018 | 7 | 7.5 | ||
result | ||||||||||
amount | currency | month | year | exchange rate | avg. rate | |||||
10 | EUR | 1 | 2018 | 1.5 | 1.3 | |||||
20 | CZK | 2 | 2018 | 7 | 7.5 |
Solved! Go to Solution.
NewColumnExRate = LOOKUPVALUE ( 'change in DKK'[exchange rate], 'change in DKK'[currency], 'Economic'[currency], 'change in DKK'[month], 'Economic'[month], 'change in DKK'[year], 'Economic'[year], 'change in DKK'[source], "actual"
)
Hi @snifer,
By my tests, the solution of AlB and Williamspsouza should be helpful.
You could download my attachment which is my test pbix. You could see the detail steps under APPLIED steps in Query Editor about Merge query.
In addition, you could create the calculated column with LOOKUPVALUE fucntion like below.
NewColumnExRate = LOOKUPVALUE ( 'change in DKK'[exchange rate], 'change in DKK'[currency], 'Economic'[currency], 'change in DKK'[month ], 'Economic'[month], 'change in DKK'[year], 'Economic'[year] ) NewColumnAveRate = LOOKUPVALUE ( 'change in DKK'[avg. rate], 'change in DKK'[currency], 'Economic'[currency], 'change in DKK'[month ], 'Economic'[month], 'change in DKK'[year], 'Economic'[year] )
In addition, based on the syntax of LOOKUPVALUE, it allows more than one search_columnName and search_value.
LOOKUPVALUE( <result_columnName>, <search_columnName>, <search_value>[, <search_columnName>, <search_value>]…)
Here is the output for two solutions.
Best Regards,
Cherry
change in DKK | |||||
currency | month | year | exchange rate | avg. rate | source |
EUR | 1 | 2018 | 1.5 | 1.3 | actual |
CZK | 2 | 2018 | 7 | 7.5 | actual |
EUR | 1 | 2018 | 2 | 1 | budget |
CZK | 2 | 2018 | 7.1 | 7.6 | budget |
the problem was I didn't notice there was an extra column specifying the source of the exchange rate ( there are double result based on different source)
2 solutions:
or I split the table into 2 different tables based on source column
or better can I modify the code
NewColumnExRate = LOOKUPVALUE ( 'change in DKK'[exchange rate], 'change in DKK'[currency], 'Economic'[currency], 'change in DKK'[month ], 'Economic'[month], 'change in DKK'[year], 'Economic'[year] )
adding the possibility to take just one parameter of source like "actual"I
what I should use early or filter?
Hi @snifer,
Have you solved your problem?
If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.
If you still need help, please feel free to ask.
Best Regards,
Cherry
NewColumnExRate = LOOKUPVALUE ( 'change in DKK'[exchange rate], 'change in DKK'[currency], 'Economic'[currency], 'change in DKK'[month], 'Economic'[month], 'change in DKK'[year], 'Economic'[year], 'change in DKK'[source], "actual"
)
Hi @snifer,
Have you tried the old school method; Create a calculated column concatenating currency, month and year and linking the tables by this created calculated column?
Best,
David
the table can be connected, so only option look to be lookup with 3 condition, but i can't make it work
merging a query you can do based on 1 column not in 3
what is the exact problem with LOOKUPVALUE()??
Column for exchange rate in Table1 (the one you show under 'economic'). I haven't tested but it should work
NewColumnExRate = LOOKUPVALUE ( Table2[exchange rate], Table2[Currency], Table1[Currency], Table2[Month], Table1[Month], Table2[Year], Table1[Year] )
based on you formula i create the one
NewColumnAVG-rate = LOOKUPVALUE ( ExchangeRates[DefaultAvgRate], ExchangeRates[CurrencyCode], ConsolidatedAccounts[CurrencyCode], ExchangeRates[Month], ConsolidatedAccounts[Month], ExchangeRates[Year], ConsolidatedAccounts[Year] )
but return an empty column
It accept just fir column then it doesn't take in consideration other 2,
can someone code this with lookup and all 3 columns?
Ok, can you just show what you tried with LOOKUPVALUE that does not work and the error message you get?
no error, just not working get wrong value,,
can you try yourself lookup in the example and if work posting to me
im not familiar with merge by formula usually i use the interface, how i can merge with the formula?
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 |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |