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.
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?
Where are you placing this calculated column? This should be placed in the bookingsteritory or do you want to return the rate in another table?
The parameter that is giving error is the Search value so it's the value you are looking in the other table.
Now this error...
I deleted and tried re-typing it all out and it isn't bringing back any errors with the formula now, but isn't bringing back results either. The New Exchange Rate column is blank.
A measure will not work with the same syntax.
can you share print screens of the exchange and booking table?