Forum Discussion
Helper IConfused Why I Cannot Make a One to One Relationship. Using the exact Column I am Linking to.
I have a table that has the columns; product category, product, price, manufacturer.
I want to basically say manufacturers with >5 products are large, manufacturers with <=5 are small.
So I went to transform data, copy and pasted the table, removed all other rows outside of manufacturer and products on the duplicated table. I assumed since it is the same exact data, when I linked manufacturer to manufacturer (to make a distinctcount measure), it would be a one to one relationship, but it is only giving me the many to many option. Any ideas why?
Format of both columns are the same.
Hi Spudder112 ,
The reason you are not able to create a one-to-one relationship between the two tables is because there are duplicate values in the 'manufacturer' column in one or both of the tables.
To create a one-to-one relationship, each value in the 'manufacturer' column of one table must correspond to exactly one value in the 'manufacturer' column of the other table. If there are duplicate values in either table, Power BI will not allow you to create a one-to-one relationship.
To resolve this issue, you can try removing any duplicate values from the 'manufacturer' column in both tables. You can do this by selecting the 'manufacturer' column in each table, clicking on the 'Remove Duplicates' button in the 'Transform' tab of the ribbon, and then reloading the data into Power BI.
Alternatively, you can create a calculated column in one or both of the tables that combines the 'manufacturer' and 'product' columns into a single column, and then use this new column to create a one-to-one relationship between the tables. For example, you could create a calculated column in both tables called 'ManufacturerProduct' that concatenates the 'manufacturer' and 'product' columns, and then use this new column to create a one-to-one relationship between the tables
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Community Support Team _ RongtieIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi Spudder112 ,
The reason you are not able to create a one-to-one relationship between the two tables is because there are duplicate values in the 'manufacturer' column in one or both of the tables.
To create a one-to-one relationship, each value in the 'manufacturer' column of one table must correspond to exactly one value in the 'manufacturer' column of the other table. If there are duplicate values in either table, Power BI will not allow you to create a one-to-one relationship.
To resolve this issue, you can try removing any duplicate values from the 'manufacturer' column in both tables. You can do this by selecting the 'manufacturer' column in each table, clicking on the 'Remove Duplicates' button in the 'Transform' tab of the ribbon, and then reloading the data into Power BI.
Alternatively, you can create a calculated column in one or both of the tables that combines the 'manufacturer' and 'product' columns into a single column, and then use this new column to create a one-to-one relationship between the tables. For example, you could create a calculated column in both tables called 'ManufacturerProduct' that concatenates the 'manufacturer' and 'product' columns, and then use this new column to create a one-to-one relationship between the tables
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Community Support Team _ RongtieIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Spudder112Thanks! That explains it.