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.
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.
Thanks so much for all your help, the relationships are created as you described now, Just one more thing seems to be off. When i combine the grouping database and weights database to make a table, the table shows the same number weight for all Groupings, which i know is not correct, it seems to still be just counting entries. For example, when trying to display Groupings by weight, it reads the same number, which is odd. Once again thanks for all your help
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.
That sorted it, Thank you very much, You've got me out of a rock and hard place
