One to Many Relationship

[nafischer90]

Hello,

I am trying to create my first pivot table. I have two tables, one containing unit serial numbers and assembly numbers. In this table serial numbers are unique and multiple serial numbers can have the same assembly number. In my second table I have each assembly number with columns containing details about the assembly. In this table each assembly number appears only once. Excel will not auto detect the relationship and when I try to manually create a relationship between the two tables using the assembly number I get the following error:

"At least one of the selected columns contain duplicate values. Both selected columns must contain only unique values to create a relationship between the tables."

I cant figure out why I get this message. Excel supports one to many relationships, so why does this message state otherwise? In my attempts to research a solution I can only find reports of a similar error where people are attempting to create a many to many relationship, but this message says something along the lines of "At least one of the selected columns must contain only unique values".

Thanks for the help!

Nick

[Olly]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[nafischer90]

Attached is a simplified version of my workbook. "Drawing No." is column in each table I am trying to relate. The first table "Master" has a column of unique "Serial"s, each with a "Drawing No.". The second table has a column with each "Drawing No." .

[Olly]

Drawing No. 08-0702 has two rows in your "Drawing" table.

[Olly]

FYI, to find this, I added a calculated column on table 'Drawing' with the following DAX:

Please Login or Register  to view this content.

Then applied a filter to this calculated column, for values >1.

[sandy666]

First Refresh All in your DataModel then try again rel

rel.jpg