relationship

[niekdp]

Hi!

I have two tables, the exchange rate per day for different currencys, and also a table with purchases currency per day:transactions w/date and currency
currencies

I would like to have the exchange rate next to the currency in the transaction table.

(there are multiple transactions per day and multiple currencies are used)

But if I want to make the connection I receive the following error:

the error

I think it's becaus you have the same value sometimes per currency. 
Is it possible to make the relationship? 

Thanks in advance!

Niek

[Anonymous]

Im sure you wont be able to solve it by creating a relationship since you can have multiple values.

I think you could maybe create a calculated column in which you have the currency + the value from it corresponding to the transaction date. 

I am new to DAX so it will be better if someone  help you with this, but i think this might work. 

[Rfranca]

hi, 

@niekdp

This error message displayed by POWER BI DESKTOP means that it has duplicate values, check for duplicate records in the CURRENCY column, or in the COMMISION_CIRRENCY column. Or empty records in these columns.

send a sample of the data to help you.

Please try and test using it. And please mark the right reply as answer if your issue has been resolved, otherwise, please feel free to ask if you have any other issue.

Best Regards,
Rfranca

[niekdp]

Dear Rfranca,

Thank you for your answer.

The problem is that you have a value per currency for each day.
So for a year, I have 365 values for GBP, USD and so on..

It's also possible that the exchange rate is the same for a couple of days..

So it isn't possible to make this relationship?

Best Regards,

Niek

[Rfranca · Accepted solution]

hi @niekdp @Anonymous

The relationship of your tabs can be solved by combining the tables. Read the link below.

Combining two queries in Power Query or in Power BI is one of the most basic and essential tasks that you would need in most of the data preparation scenarios. There are two types of combining queries; Merge, and Append.

relationship-in-power-bi-with-multiple-columns

Please try and test using it. And please mark the right answer as answer if your issue has been resolved, otherwise, please feel free to ask if you have any other issue.
 
Best Regards,
Rfranca

[niekdp]

Dear Rfranca,

thank you very much, this worked!

Kind regards

Niek

[niekdp]

Dear Rfranca,

Ik can merge the colums, but per currency I have a fixed rate.
Instead of a different rate per day, I see a fixed rate for the whole period.

Is it possible to fix this problem?

Kind regards

Niek