Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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.
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.
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.
Hi @Anonymous,
I sugest that you use Query editor to merge your tables before the load.
Hope this helps.
Mariusz
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |