Forum Discussion
Post PatronCreate Relationships with tables result does not create relationships
powebi, several tables contain financial year data. eg
table FinancialYear2024_2025
table FinancialYear2023_2024
table FinancialYear2022_2023
etc
in Model View I created relationships with the 1st table FinancialYear2024_2025 and other tables, and relationshpis successfully created.
FinYear2024_2025, day_of_week_name - melbourne_holidays, Day. Many to many, Both
FinYear2024_2025, date - melbourne_holidays, date. Many to many, Both
FinYear2024_2025, Hour - Time12hr, HourGroup. Many to one, Single
FinYear2024_2025, month_name - FinMonthSortOrder, month_name. Many to one, Single
FinYear2024_2025, month_name - FinYears, month_long. Many to one, Both
The next table FinancialYear2023_2024, I am creating the same relationships with the tables.
in Model View, create realtionships
On Manually create the relationships, click on Manage Relationships.
In the Relationships dialog box, click Save.
The results, the relationships do not create.
in the 1st table, have I incorrectly setup the relationships. or is it something else. see screen captures
any info on this one, would be great
TIA
apologies danextian SEMattis I was looking into this.
This is now resolved.
The problem was the table relationships and using Many to many.
3 tables
FinancialYear. contains financial years, quarters, month, quarter numbers, etcsame table fields and layout used for:
FinYear2024_2025. stores all 2024-2025 financial data
FinYear2023_2024. stores all 2023-2024 financial datatable FinancialYear, with table FinYear2024_2025 create relationship using field month create Many to many relationship. the result successfully created relationship
and, using same table FinancialYear, with table FinYear2023_2024 create relationship using field month create Many to many relationship. the result relationship not created, either by drop & drag, or manually.Resolved
I watched these youtube videos.
redesigned the tables to have a unique key, and then create relationships using One-to-manyHandling MULTIPLE fact tables in Power BI
https://www.youtube.com/watch?v=TnyRsO4NJPc2 EASY WAYS to FIX MANY to MANY relationships in POWER BI // Beginners Guide to Power BI
https://www.youtube.com/watch?v=1L66tcLaRQ0Many thanks for your assistance and suggestions
6 Replies
- SEMattis
Advocate III
I'm not 100% sure what the issue might be but a guesstimate would be that, by having numerous Many-to-Many relationships between the tables in the data model, is introducing ambiguity and circular relationships causing the relationship in the 1st screenshot to remain inactive.
As a suggestion have a look at the following page and Chapter 15 for circular references for a potential solve on your problem: Circular References in Data Modeling - Power BI Training Australia
dd88apologies danextian SEMattis I was looking into this.
This is now resolved.
The problem was the table relationships and using Many to many.
3 tables
FinancialYear. contains financial years, quarters, month, quarter numbers, etcsame table fields and layout used for:
FinYear2024_2025. stores all 2024-2025 financial data
FinYear2023_2024. stores all 2023-2024 financial datatable FinancialYear, with table FinYear2024_2025 create relationship using field month create Many to many relationship. the result successfully created relationship
and, using same table FinancialYear, with table FinYear2023_2024 create relationship using field month create Many to many relationship. the result relationship not created, either by drop & drag, or manually.Resolved
I watched these youtube videos.
redesigned the tables to have a unique key, and then create relationships using One-to-manyHandling MULTIPLE fact tables in Power BI
https://www.youtube.com/watch?v=TnyRsO4NJPc2 EASY WAYS to FIX MANY to MANY relationships in POWER BI // Beginners Guide to Power BI
https://www.youtube.com/watch?v=1L66tcLaRQ0Many thanks for your assistance and suggestions
- danextian
Super User
Please mark your post as a solution.
- danextian
Hi dd88
Can you please show a diagram of the relationships and mark which ones you're trying to create but can't. I am susppecting it is because you are trying to create bi-directional relationships which can cause ambiguity. You can actually change the relationship to single direction even if it is many-to-many.
- dd88
hi danextian
have mapped it, this layout maybe easier ..
table FinYear_2024_2025 (main dataset) table holidays (stores annual holidays) field Date field Date 25/04/2025 25/04/2025 table FinYear_2024_2025 table finYears (stores financial years, months & quarters) field month_name field month_long_name August August table FinYear_2024_2025 table Time12hr (stores 24hr time & converts to 12hr) field Hour field hourGroup 16 16 table FinYear_2024_2025 table FinMonthSortOrder (sort order by months in financial year. starts with 0 = July ... 11 = June field Month field month-name August August - SEMattis
The reason why you cannot activate the relationship is that you already have 1 active relationship between the two tables and can therefore not activate the relationship between day-of-week and Day. If you still want to "use" the relationship between melbourne_holidays and the FinancialYear2024_2025 table you can use the USERELATIONSHIP() function to activate the specific relationship between the day-of-week and Day columns in the respective tables.
The function, provided you are calculating something would look like:
= CALCULATE(SUM(InternetSales[SalesAmount]), USERELATIONSHIP(FinancialYear2024_2025[day-of-week], melbourne_holiday[Day]))
Using the above you can leave the relationship inactive.
