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.
Judging by the columns in your base table, like fiscal year, I'm guessing that you have multiple entries in base table for each part group, but only one entry in the weights table for each part group. If that is the case then you can create a one-to-many relationship from weight to base table using the part group column, but first you will need to delete the relationship from group to base table.
You won't need that relationship, as the filters will flow from group to weight to base table, but having that group -> base table relationship in place at the same time as the others would introduce ambiguity, which Power BI won't let you do
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.