Skip to main content
Showing results for 
Search instead for 
Did you mean: 

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.

Frequent Visitor

How to auto convert to amount with the Exchange rate based on the Year selected in the Slicer

Hi PBI Experts,


I create two tables below. The Col Amount in the Data table is in original currency and split by year. I am not able to create a relationship between two tables due to there is no singapore with unique value.


The thing I want to achieve is I would be able to make the amount in the matrix table covert based on the respective rate of the year I have selected in the slicer. For instance, when I select year 2021, the amount of every year (2020, 2021, 2022) will be auto converted based on the 2021 rate of the respective currency.


Can you please advise how I can do that? By using a Dax formula or I need to modify the table and create a column in the Data table to convert based on the year I select?


I want to attached the files but I cannot locate where I can add the files in this post.


Table - Data



Table - Currency



Model : unable to create relationship between 2 tables due to there is no any column with unique data




Matrix Table in Original Currency. Ideally the data in Matrix table should auto convert to the USD based on the rate of the year.




Frequent Visitor

@amitchandak  Can I know if there is any solution for the calculation issue I have encountered in your knowledge?


Frequent Visitor

@amitchandak Thanks for the suggestion. However, the solutions you have suggested should be meant for convert the data of the year with corresponding yearly exchange rate, meaning 2020 data with 2020 exchange rate, 2021 data with 2021 exchange rate.


What I need is to convert the data of every year (data of 2020, 2021, 2022) with the year of change rate I have selected from the slicer. For instance, when I select year 2021 under the slider, all the data (data of 2020, 2021, 2022) will be converted with 2021 exchange rate of respective currency. if I select year 2022, all the data (data of 2020, 2021, 2022) will be converted with 2022 exchange rate of respective currency. 


Hope my ellaboration above helps to understand what I try to achieve.

Super User
Super User

@kelvin-lkh , Create a combine key inboth Tables


key = [Year] & "-" & [Curr]


Join both table on this 


and then you can get rate in data table like in a new column

Rate = related(currency[Rate])


or in measure


Amount = Sumx(Data, Data[Value]*related(currency[Rate]) )


refer 4 ways (related, relatedtable, lookupvalue, sumx/minx/maxx with filter) to copy data from one table to another

Helpful resources

Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City


Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.