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.
Hi,
i have a column with values that has multiple currencies such as:
AED
USD
EUR
SAR
and i want to create a new column that will convert all of them in USD and one more that will convert all the data in EUR.
how is this possible?
Solved! Go to Solution.
hi @hadjigeo
If so, just adjust the formula to create a measure instead of column
lastest rate = MAXX(TOPN(1,FILTER('Dim daily rates','Dim daily rates'[bpc_name]=MAX('Table'[bpc_name])),'Dim daily rates'[bpc_ExchangeDate],DESC),[bpc_ExchangeRate])
Regards,
Lin
Do you have Exchange rate date and currency wise
yes I have an exchange table with daily rates and currency. see below an example of the table
hi @hadjigeo
For your case, you could try this way as below:
Step1:
Add a bpc_name in your basic fact table, you could add it in edit queries or use this simple dax
Column = 'Table'[Currency]&"-EUR"
Step2:
Add a lastest rate column in fact data table as below:
lastest rate = MAXX(TOPN(1,FILTER('Dim daily rates','Dim daily rates'[bpc_name]='Table'[bpc_name]),'Dim daily rates'[bpc_ExchangeDate],DESC),[bpc_ExchangeRate])
Now you could use this rates column in calculated column or measure to get your requriement.
here is sample pbix file, please try it.
Regards,
Lin
Hi @v-lili6-msft for the step 2 when i create a table it doesnt let me to use the MAXX.
i Am using a directquery, does this has to do anything with that?
hi @hadjigeo
If so, just adjust the formula to create a measure instead of column
lastest rate = MAXX(TOPN(1,FILTER('Dim daily rates','Dim daily rates'[bpc_name]=MAX('Table'[bpc_name])),'Dim daily rates'[bpc_ExchangeDate],DESC),[bpc_ExchangeRate])
Regards,
Lin
whatif exchange rates table is missing dates. Should i fix the exchange rate table to fill up the missing dates or use measure to calculate the value in USD currency with reference to native currency and transaction dates.
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |