cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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
Greg_Deckler
Super User
Super User

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. 


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

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. 


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Anonymous
Not applicable

Hello Greg,

 

Thank you very much for your response 🙂

 

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.

Mariusz
Community Champion
Community Champion

Hi @Anonymous,

 

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

 

Hope this helps.

Mariusz

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Top Solution Authors