Are you ok with letting the exchange rate be held in a parameter? The downside is that you'll need to manually update the parameter values if the exchange rate changes. The upside is you don't need to establish a relationship between the data sources. For this method:
Create a new parameter of type float. You can add the exchange rate values from your field, then manually type in the "Display As" with the appropriate descriptions.
Your transformation calc will be simple:
Hope this helps,
Here's another way of doing it that would let your exchange rates remain in a table and be more easily refreshable. There is still some manual setup mapping the parameter to the Exchange Rate values, but I assume there won't often be new currencies to add, and this way after the manual setup, you can just refresh your data sources when the exchange rates update.
I've attached the Excel and Tableau workbooks.
Transpose your Exchange Rate table so the exchange rates are set up as measures:
Now bring your Values table in, then join the Alt ExchangeRate table simply on a calculated 1 = 1. This has the effect of joining every record from both tables, which is fine since Alt ExchangeRate basically has no dimensions and only measures.
Use your original ExRate text parameter to map the parameter choice to the measure:
Now the transformation calculation:
Good idea, but unfortunately I need to take the exchange rates from the database. They are updated frequently.
Regarding joining via 1=1. This will double the size of my data set, won't it? I have hundreds of currencies in my real data set... So I am afraid this won't work:/
Usually a join like that would more than double the size of your dataset and isn't advisable, but in this case there are measures only, no dimensions. It would add as many columns to your data set as your have currencies, but it wouldn't add any rows.
Check out this article on using Custom SQL with parameters. You should be able to use the join type of my second solution if you use custom sql and your ExRate parameter to grab the exchange rate directly from the database. So in this case it would add a single column to your main dataset: the exchange rate selected from the parameter.
For this solution, you need to maintain a live connection to your database. Every time your use selects a new currency from the parameter dropdown, a query will go to your database to retrieve that one currency value. You'll have to check if Tableau has the Custom SQL option for your particular database.
I created a quick example of this using my local postgres database. If this sounds like a solution that will work for you (Tableau allows custom sql for your database and your end solution can maintain a live connection), I can detail the steps.