Relationship query

[NLEIGH]

Hi,

I am still a novice at Power BI and trying to setup a relationship between 2 data sets and the common link is the date, column WC 2, however I am getting a error when trying to set this up, screenshot below.  Can anyone advise on what I need to do to rectify this issue.  

I'm not sure if I have the cardinality or cross filter direction options correct.

Many thanks in advance for your help.

[mark_endicott · Accepted solution]

@NLEIGH - This is a warning rather than an error.

It's just letting you know that neither of you columns has unique values, and therefore the relationship will be many to many, which is usually bad for the performance of the report. 

It will still let you create the relationship, but the best way to do this would be to create a date table that joins to both of these tables and becomes the filter for them. 

More advice on creating a date table can be found here: https://www.youtube.com/watch?v=WybnTHDl-AM

If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!

[danextian]

Hi @NLEIGH 

What you’re seeing is not an actual error, but a warning. The WC2 field in both tables doesn’t have unique values, which automatically sets the relationship cardinality to many-to-many. This setup can be valid in certain cases, but you should only proceed if you fully understand the implications. Please refer to this: Apply many-to-many relationships in Power BI Desktop 

[NLEIGH]

Many thanks for your response

[v-dineshya · Accepted solution]

Hi @NLEIGH ,

Thank you for reaching out to the Microsoft Community Forum.

You are trying to create a relationship between two tables based on a date column, but you are encountering a "Many to Many" cardinality issue. This is typically because both tables contain multiple rows with the same date value in the "WC 2" column.

Please follow below steps to fix the issue.

1. "Many-to-Many" relationship means that both tables have multiple entries for the same value in the "WC 2" column, this relationship could cause performance issues and incorrect results.

2. Create a Date table(only unique date values) and relate it to both tables using a "One-to-Many" relationship between the Date table and your tables (RawData and C&C 2025).

3. From RawData table to Date table the relationship be like Many to one relationship and from Date table to C&C 2025 the relationship be like one to Many relationship.

4. The default setting is "Single" directional filtering from RawData to C&C 2025. If you need to filter both tables based on the other (bidirectional filtering).

Please refer community threads.

Solved: Re: Many to Many Relationship - Microsoft Fabric Community

Solved: Re: Join 2 different tables without removing uniqu... - Microsoft Fabric Community

If my response has resolved your query, please mark it as the "Accepted Solution" to assist others. Additionally, a "Kudos" would be appreciated if you found my response helpful.

Thank you

[NLEIGH]

many thanks for your help, I believe the steps you have given will solve the issue.

[mark_endicott · Accepted solution]

@NLEIGH - This is a warning rather than an error.

It's just letting you know that neither of you columns has unique values, and therefore the relationship will be many to many, which is usually bad for the performance of the report. 

It will still let you create the relationship, but the best way to do this would be to create a date table that joins to both of these tables and becomes the filter for them. 

More advice on creating a date table can be found here: https://www.youtube.com/watch?v=WybnTHDl-AM

If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!

[BITomS]

Hi @NLEIGH ,

Taking a look and understanding model relationships and cardinality would be useful I think: https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-relationships-understand

It looks like you are effectively trying to create a Fact to Dimension table join which requires a One-to-Many relationship so that the model knows which path to take when filtering etc. With a many to many relationship, the model will not know what defined path to take, which is why the error message is appearing - it will not allow this.

Another question you would need to ask of your tables is if you have one table with unique values (Dimension table) so again the model does not have conflicting paths to take when filtering.

Hope this helps.

[NLEIGH]

many thanks for your response

[BITomS]

I stand corrected: 'warning' rather than 'error' as others are saying!