Forum Discussion
Help with relationships
Sounds like in your Weights database that the Part Group is not unique. Best way to tell: Add that column THREE times to a table visual. Leave the first one as it is, change the aggregation of the second to DISTINCT COUNT, and change the aggregation of the third to COUNT. Now look at the bottom numbers. If the COUNT = DISTINCT COUNT then the Part Group values are unique.
If you can get the Part Group values to be unique in the Weights database, then the relationship can be One-to-One, which makes sense for your mode.
If you had ONE entry in Weights per Part Group, then the relationship to Groups would default to one-to-one, not one-to-many. (Power BI inspects the data in the columns before trying to assign the relationship cardinality. This is awesome becuse it means you can drag from one table to the other in either direction and Power BI will just figure it out.)
If a one-to-one relationship is not supported in the join to Groups, then you won't be able to get a one-to-many going from Base to Weights.
What is the uniqueness of the Parts Group column in each of the three tables? In Base, we assume it is NOT unique (any one value can show up many times). In Groups, we assume it IS unique (any one value shows up only once, making it unique and therefore able to be the "ONE" side of a one-to-many relationship. What is it for the Weights table? This is critical for this discussion.
I seem to have additional part numbers in my Grouping database, than my weight database, is that the determining factor in not having a one to one relationship?
No. If you have members A, B, C, and D in one table, and members B, C, D, and E in the second, it will determine a one-to-one relationship. But if the second has B, C, D, and D, then it will determnine a one-to-many. I bet it also does one-to-many if it finds B, C, D, E, and E (even if E is NOT in the ONE side of the relationship). this is because it know that the column does not have unique values.
I have had many a model fail refresh because the new data coming in would break the relationships defined. This is a pain. The only way to figure it out is to remove the relationship, refresh the data, inspect the new data for duplicates, remove the duplicates from the source, refresh again, then re-add the relationship.
Relationships are critical in any data modelling, not just Power BI. SQL Server (and most RDBMS) will maintain FOREIGN KEYS for you and keep things in line. Not so Excel, CSV, and SharePoint.
Hope that helps.