Forum Discussion
Condition Statement
Hello @karabryan ,
Could you please try to use firstnonblank in your formula like:
LOOKUPVALUE( FIRSTNONBLANK(Currency[NewRate],1), Currency[NewSourceTarget], Booking[NewSourceTarget] Currency[Date], Booking[Date] )If this post helps, then consider Accept it as the solution to help other members find it faster.
Best regards
Dedmon Dai
Hi karabryan ,
In order to have a more permanent solution believe you need to do the following:
- Create a measure with the following code:
ExchangeRate = MIN(ExchangeRateTable[ExchangeRate])- Now create a new measure to make the calculation:
Valueconverted = SUMX(Table, Table[Amount] * [ExchangeRate])Be aware that you need to have the columns from your many-to-1 relationship from the oneside on the visualization.
You SUMX table need to be adjusted to your model.
this is just a generic solution needs to be adjusted to your model and needs.
What is the best way to do that? This is one page in a .pbix with 60 tables and many pages of visuals. Last night I tried to use look-up functions but need multiple nested functions and that didn't work for me as expected. I also tried to merge the two tables in power query but that also didn't work because the exchange rate table had companies listed in it and although there is a relationship between the two, it didn't match them up correctly. I'm removing the company info from the exchange rate table because it is irrelevant and seeing if that helps. I can try to make a mock up file in the mean time if there is a way to isolate just that portion of my project.
Hi karabryan ,
Since your model is so large let's take it step by step, can you answer me this:
- What is the measure you are using for making the conversion?
- Do you have many-to-many relationship between the exchange rate table and your fact table?
I made some changes and now have a table for the CurrExRate that only has the Date, NewSourceTarget(eitherUSDUSD or RMBUSD) and the new rate (1 or the RMBUSD conversion rate). I created those with conditional columns in PowerQuery. Then have a table called CurrencyType that is just the RMBUSD and USDUSD currency type values so I can create a relationship to the Booking Territory table without using the many-many relationship.
ERP CurrExRate Table:
Bookings Territory Table
Relationships (I had tried using just SourceCurrCode and TargetCurrCode earlier after watching a tutorial but that didn't fit my needs). I have relationship from ErpCurrExRate to the Date table and to the Currency Type Table. Bookings Territory is related to the Date table, the Company table and the Currency Type.
I thought I could merge the tables now in Power Query but that didn't work. I could possible use a lookup now in DAX but I'm not sure how to lookup date and then currency type to get the correct value.
Hi karabryan ,
The lookup should look similar to this:
LOOKUPVALUE( Currency[NewRate], Currency[NewSourceTarget], Booking[NewSourceTarget] Currency[Date], Booking[Date] )Be advise that this may need some adjustments.
It doesn't seem to like me pulling from the Bookings Territory table when I use the lookup function. I'm adding this as a new column. Should I have tried using a measure?