cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jrpgo Frequent Visitor
Frequent Visitor

Avoiding Many-To_many relationships

I have 3 tables, “Accounts”, “Currency” and “Banks”.

 

In the accounts “Accounts” table I have the current Balance for each account at a certain date. In the “Currency” table the Rate at which a “Currency” was transacted at a certain Date.
In the “Banks” table I have each Account with its respective Currency and the respective Bank.

 

The problems begin when I create the relationships between the tables. I need to multiply the Balance of each account by its corresponding Currency in each respective date, however, these Dates may not be an exact match, I will be using the average of the Balance in each month and the average of each Currency for each month.

This is also forcing a Many-to-Many relationship. I must connect the Currency Column from the “Banks Table” with the Currency Column of the “Banks Table” and finally connect the Account Column from the “Banks Table” to the to the Account Column from the “Accounts Table”.

 

Understandably, Power BI will not allow me to create any relationships between the “Currency Table” and any other table after creating the Many-To-Many relationship between the Banks Table and Currency Table.

Is there any suggestion for a workaround these problems?

 

I am not accustomed to submitting questions, if there is any info that is confusing or lacking please let me know.

 

Thank you in advance for your help.Tables.PNG

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Avoiding Many-To_many relationships

I think that @Mariusz's suggestion is a good one but it may be problematic if the dates aren't an exact match. Fuzzy matching may be an option but even if you merge it all into a single fact table, you may still want to have your dimension tables as separate tables. You can do this using DISTINCT to create a new table via DAX. I would recommend separate tables for distinct accounts, banks and currencies. You may be able to use these as "bridge" tables to avoid many-to-many relationships. You should also have a separate date table. You can use CALENDAR or CALENDARAUTO to create a date table. 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


3 REPLIES 3
Mariusz Senior Member
Senior Member

Re: Avoiding Many-To_many relationships

Hi @jrpgo,

 

I sugest that you use Query editor to merge your tables before the load.

 

Hope this helps.

Mariusz

Super User
Super User

Re: Avoiding Many-To_many relationships

I think that @Mariusz's suggestion is a good one but it may be problematic if the dates aren't an exact match. Fuzzy matching may be an option but even if you merge it all into a single fact table, you may still want to have your dimension tables as separate tables. You can do this using DISTINCT to create a new table via DAX. I would recommend separate tables for distinct accounts, banks and currencies. You may be able to use these as "bridge" tables to avoid many-to-many relationships. You should also have a separate date table. You can use CALENDAR or CALENDARAUTO to create a date table. 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Highlighted
jrpgo Frequent Visitor
Frequent Visitor

Re: Avoiding Many-To_many relationships

Hello Greg,

 

Thank you very much for your response Smiley Happy

 

I had already created the Dates Table to avoid any "Holes" in the information so that I could use Time related functions functions further ahead. I have been doing some further research in other platforms and found this solution (I will leave it here in case you ar interested or for another user that might have a similar problem): https://businessintelligist.com/2015/03/17/power-bi-tutorial-how-to-implement-currency-conversion-us...

This has the advantage of only creating 1 Dimensions table (Currency), however I haven't been able to make the Lookup work, but that will have to be another topic.

 

Anyways thank you for your help, I will start doing some trial and error to see if it works.