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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
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
March Fabric Community Update

Fabric Community Update - March 2024

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

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.