Forum Discussion
Help with relationships
Hi everyone,
I'm running into trouble when trying to create effective relationships. I have three excel files, Base data, Groupings database and Weight database. Base data contains all product information including "Part Group", which is essentially product name. In the groupings database i simply have the Part groups organised into specific groups. These two pair fine when creating a powerBI report however the trouble is with the weight database. I have all Part Groups with their relevant Weight in the weight database, but when i try to create a relationship between this database and the base data database, an error shows saying i need a unique column? but the weight values are unique? Any help would be very much appreciated, thank you.
I have attatched a snapshot of the current relationships below.
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.
20 Replies
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.
Thank you very much! Do you mind if i ask how can i make the part groups unique? If the grouping database contains only Groupings and the list of Part groups, why can it form a relationship with the Base data, But the weight database, which has a list of part groups and weight cannot? I understand that i am most likely explaining this in a confusing way so i apologise.
In your Power Query, try aggregating the Weights Database by Part Group. Include aggregations like Average, Min, Max, and Row Count.
Think about it, you have identified a single Part Group in the Grouping database, and you want to know its Weight, so you traverse the relationship to the Weights database and see that there are TWO or more weights listed for that one Part Group. Which to choose? You have to get the Weights database down to ONE ROW per Part Group and an AGGREGATION in Power Query is an easy way to do it.
I am differing by just 1 inbetween count and count distinct of the part groups on the weight database. But in my excel file there is 2656 entries, According to the count function there is 2657
If you Sort by the COUNT column (descending) you should see the one item with multiple weights. Go into Excel, delete one of the duplicates, refresh the table, then edit the relationship to be one-to-one.
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
Your assumptions would be correct, thank you i wasnt thinking of it like that. I do however have a report built on the Base data and Grouping database relationship. As to display this data coherently, i need to display by groupings as there is so many part groups. Is there any way i can have both of the relationships connected?
As long as you have a path from Grouping to Base Data then it will still work, it doesn't matter that it flows through the Weight table. You can still use functions like RELATED and RELATEDTABLE as well
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?
