Relationship created, but not implemented

[barjoman]

I'm trying to link two tables together and create a pivot table based on the data in both tables. Each table has as its first column a value which is unique within the table and shared with other tables (this is the field I have used to link the tables). The formats for Table1 and Table2 are as follows:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Type
[/TD]
[TD]1X
[/TD]
[TD]2X
[/TD]
[TD]3X
[/TD]
[/TR]
[TR]
[TD]A-B-C
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]A-B-D
[/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[TD]6
[/TD]
[/TR]
[TR]
[TD]A-E-C
[/TD]
[TD]7
[/TD]
[TD]8
[/TD]
[TD]9
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Type
[/TD]
[TD]1Y
[/TD]
[TD]2Y[/TD]
[TD]3Y[/TD]
[/TR]
[TR]
[TD]A-B-C
[/TD]
[TD]11
[/TD]
[TD]12[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]A-B-D
[/TD]
[TD]14[/TD]
[TD]15[/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD]A-E-C
[/TD]
[TD]17[/TD]
[TD]18[/TD]
[TD]19
[/TD]
[/TR]
</tbody>[/TABLE]

When I create the Pivot Table, Type and [values] go in the Row Label (I used Type from Table1). The values are 1X and 1Y, so the resulting Pivot Table SHOULD look like this:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Type
[/TD]
[TD]Values
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A-B-C
[/TD]
[TD]Sum of 1X
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]A-B-C
[/TD]
[TD]Sum of 1Y
[/TD]
[TD]11
[/TD]
[/TR]
[TR]
[TD]A-B-D
[/TD]
[TD]Sum of 1X
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]A-B-D
[/TD]
[TD]Sum of 1Y
[/TD]
[TD]14
[/TD]
[/TR]
[TR]
[TD]A-E-C
[/TD]
[TD]Sum of 1X
[/TD]
[TD]7
[/TD]
[/TR]
[TR]
[TD]A-E-C
[/TD]
[TD]Sum of 1Y
[/TD]
[TD]17
[/TD]
[/TR]
</tbody>[/TABLE]

Unfortunately, it looks like this:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Type
[/TD]
[TD]Values
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A-B-C
[/TD]
[TD]Sum of 1X
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]A-B-C
[/TD]
[TD]Sum of 1Y
[/TD]
[TD]42
[/TD]
[/TR]
[TR]
[TD]A-B-D
[/TD]
[TD]Sum of 1X
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]A-B-D
[/TD]
[TD]Sum of 1Y
[/TD]
[TD]42
[/TD]
[/TR]
[TR]
[TD]A-E-C
[/TD]
[TD]Sum of 1X
[/TD]
[TD]7
[/TD]
[/TR]
[TR]
[TD]A-E-C
[/TD]
[TD]Sum of 1Y
[/TD]
[TD]42
[/TD]
[/TR]
</tbody>[/TABLE]

Any ideas?

 

[scottsen]

I am guessing the relationship is going the wrong direction?

 

[barjoman]

I am guessing the relationship is going the wrong direction?

You are correct. I used the Type from Table2 and it worked. So the question really is how do you create a one-to-one relationship in Powerpivot, and the answer seems to be that you can't.

 

[scottsen]

They work fine, but you need to edit the relationship to swap the direction.