Create Relationships with tables result does not create relationships

[dd88]

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

[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.

[SEMattis]

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

[dd88 · Accepted solution]

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, etc

same table fields and layout used for:

FinYear2024_2025. stores all 2024-2025 financial data
FinYear2023_2024. stores all 2023-2024 financial data

table 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-many

Handling MULTIPLE fact tables in Power BI
https://www.youtube.com/watch?v=TnyRsO4NJPc

2 EASY WAYS to FIX MANY to MANY relationships in POWER BI // Beginners Guide to Power BI
https://www.youtube.com/watch?v=1L66tcLaRQ0

Many thanks for your assistance and suggestions

[danextian]

Please mark your post as a solution.