how do I properly create a relationship between 2 tables

[Anonymous]

I am having a problem with the relationship with 2 tables.  I have one table and I created a new table by copying and pasting the original table and named it differently; As I will be using it differently.  KVC1 AND KVC2.  KVC1(7 days of data) and the KVC2(365 days of data)

Now the problem is the relationship.  I wanted to do which is common sense to me the account No. fields in each, but it gives me the followng message:  

This relationship has cardinality Many-Many.  This chould only be used if it is expected that neither column (Account No and Account No) contains unique values... or The cardinality you selected isn't valid for this relationship.

How do I fix this relationship?

[Anonymous · Accepted solution]

Hi  @Anonymous ,

According to the error message: there is no shared dimension between the two tables, and the fields of the join relationship do not have unique values.

Try adding a calculated table and using many-to-one relationship:

Solved: Create join table for many to many relationship DA... - Microsoft Fabric Community

https://radacad.com/creating-a-shared-dimension-in-power-bi-using-power-query-basics-and-foundations-of-modeling

Solved: Re: Unique values from two columns - Microsoft Fabric Community

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you so much!

[lbendlin · Accepted solution]

You need to create a proper Account dimension table (with unique Account No values) and then comtrol your two tables through it.

Avoid linking fact tables directly.

[Anonymous]

This helped.  Thanks.

[Anonymous · Accepted solution]

Hi  @Anonymous ,

According to the error message: there is no shared dimension between the two tables, and the fields of the join relationship do not have unique values.

Try adding a calculated table and using many-to-one relationship:

Solved: Create join table for many to many relationship DA... - Microsoft Fabric Community

https://radacad.com/creating-a-shared-dimension-in-power-bi-using-power-query-basics-and-foundations-of-modeling

Solved: Re: Unique values from two columns - Microsoft Fabric Community

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.