Many-One Relationship when there are Unique values in both tables

[Anonymous]

I am attempting to link togther two tables that both have unique values, however, it is not allwoing me to make a one:one relationship- instead making a many:one relationship.

I have checked in Excel & Power Bi itself and the table on the many side has all unique values with no blanks 

See above I have ordered count of item#/cmi# in descedning order and the highest count is one and therefore there is all unique values.

Thanks in advance for any help,

Adam P

[v-tangjie-msft]

Hi @Anonymous ,

There are two ways to edit a relationship in Power BI.

The first method to edit a relationship is using the Editing relationships in the Properties pane in Model view, where you can select any line between two tables to see the relationship options in the Properties pane. Be sure to expand the Properties pane to see the relationship options.

The other method of editing a relationship is using the Relationship editor dialog, which you can open many ways from within Power BI Desktop.

Please refer to the following document for more information.

Create and manage relationships in Power BI Desktop - Power BI | Microsoft Learn

Best Regards,

Neeko Tang

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

[Anonymous]

Hello Neeko,

Thanks for your reply.

I know how to edit teh relatiosnhips but Power bi is not accepting the one:one cardinality taht it shoudl be.

For some reason, it is detecting dupliacte values in one of the tables which are unique.

Kind Regards, Adam P

[v-tangjie-msft]

Hi @Anonymous ,

This is due to the design, and one-to-one relationships are not supported when there are duplicate values in the table.
You can use the values() function or the distinct() function to create a table with deduplicated values. Then establish a one-to-one relationship with the new table.

Please refer to my PBIX file.

Best Regards,

Neeko Tang

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

[Anonymous]

Hi @v-tangjie-msft ,

I think the issue was buggy source data- a new file I loaded in is now allowing the one:one relationship.

Thank you very much for yor help, I apprecite you taking the time.

Adam P

[Anonymous]

@v-tangjie-msft 

I created a new table using Value Function and Distinct Function respectively and it is still not allowing me to create one:one relationship with either of the calculated tables

[Anonymous]