Help with relationships

[Kieran-q_20]

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.

[johnt75]

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

[ToddChitt]

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.

[Kieran-q_20]

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? 

[ToddChitt]

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.

[Kieran-q_20]

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? 

[johnt75]

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

[ToddChitt · Accepted solution]

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.

[Kieran-q_20]

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 

[ToddChitt]

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.

[Kieran-q_20]

That sorted it, Thank you very much, You've got me out of a rock and hard place 

[Kieran-q_20]

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. 

[ToddChitt]

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.

[Kieran-q_20]

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 

[ToddChitt]

What do you mean by "combine the grouping database and weights database to make a table"?

Do you mean you are making a table visual on a report page, and adding the Grouping from the Group table and the Weight from the Weights table?

Do you have a one-to-one relationship set up between the two?

Can you show some sample data?

[Kieran-q_20]

My apologies for the late response, my work shuts down for the weekend. Yes sorry i meant to say im making a table visual on a report page by adding the groupings from the grouping table and the weight from the weight table. 

I do indeed have a 1 to 1 relationship between them and below is a snapshot of the relationships 

I'm not sure if i can show much sample data, but i can show the difference in the actual returns table and the Weight table.

As you can see the weight table, which is on the right is identical across all rows, but the reutrns table works perfectly. The actual reutnrs table uses, Actual returns from the Base datatable, Groupings from the grouping database table and quarter from the base data table. 

The weight table however uses, quarter from the base data table, groupings from the grouping database and Gross weight from the weight database. 

[ToddChitt]

As displayed, the Groups table cannot 'filter' the Weights table because the FILTER DIRECTION on the relationship (from Base to Weights) is "one-way". 

Two options:

1. Remove the relationship from Base to Weights, and activate the relationship (dotted line = not active) between Groups and Weights.
2. Change the Cross Filter Direction of both active relationships from "Single" to "Both" 

As a general rule, I never keep around in-active relationships in my models. They just clutter things up and don't contribute to anything.

Either one of the above two actions should allow the Groups database to 'filter' the Weights database. 

Hint: Table can only 'filter' other tables when the relatinship lines 'point' from one to the other. In your current state, Groups 'point to / can filter' Base, and Weights 'points to / can filter' Base. With bi-directional filtering turned on, Base would be alble to filter the other two. The net result of that would be that Groups would 'filter' Base, and Base would in turn 'filter' Weights.

Hope that helps.

[Kieran-q_20]

I have removed the Base to weights realtionship and Made the Group and weight realtionship active. Is the data im trying show maybe the problem? Just to clairfy, partgroups have an assigned weight in the weight table. The partgroups are then grouped together in the groupings table. But when i try to display weight by grouping in a report, It doesnt display what i want. Is that because its not using the partgroups from the base data table? 

[ToddChitt]

I think it's time to show some sample data from each of the three tables, and maybe a mock-up of what your target visual should look like.

[Kieran-q_20]

You're right, I'll just go through each sources of the data quickly. 

Above is the weights table data. The list is a lot longer but as you can see the part group is displayed alongside its relevant weight. 

Above is the groupings table, Here the the part group is displayed alongside its relvant grouping, There are many different groupings as the list goes on such as Drive, Rack, Cards etc. 

Finally, above is a snippet of the base data. The part group is displayed here also. Per my earlier line of questioning, you can see the actual returns are displayed in the base data on column R. Creating a table with groupings and returns thus turned out fine. The problem lies with displaying the weight by group effectively. 

I think the best way to display the visual i am looking for is above. The returns dollar amount by grouping is the table on the left and that table is perfect. But on the right is the weights table which is for some reason displaying identical values. 

[ToddChitt]

What are Q1 and Q2? Calendar Quarters? Your Weights data is NOT dependent on time. 

If you have a one-to-one relationship between Groups and Weights (on Part Group) and you build a table visual showing the Group and sum of (Weight), you should get correct data. Take the other columns out of the table.