I made a relation 1 to many between to table but when I try to use it in a pivot table it fails. I get the usual yellow message saying it may lack a relation. When I let it try to detect one it fails to find anything possible and when I check the existing ones, mine is there and done properly.

Here is a mock-up of the situation :

These are the simple tables :

To send the values to the data model I make those tables go through Power Query because the real situation requires Power Query. No additional steps are added, it's just the "From table or range" source.

Then, in Power Pivot, I create the relation with the diagram view :

When I then create a pivot table, the relation has not impact :

I also tried to add a measure and use it at as the value but to no avail :

Price:=SUMX(Rqt_Ref;Rqt_Ref[C_B]*Rqt_Ref[C_C])

I did not create the pivot table with the original tables instead of the one that were send to the date model because I want this to work even if there are more than 1 048 576 rows.

I have no idea as for why it fails and I welcome any comment.

Edit : Since I don't want to do the job in Power Pivot with DAX (related) I did the merge with Power Query Merge queries :

CodePudding user response：

I think you might be misunderstanding how PowerPivot works. Your model should be related on C_A and C_4 for a start.

When I add fields from the dimension (C_A) and then one from fact C_4, it will show a cross join of everything and appear not to be working. Excel does this until you place a value in the values well.

Once I drop a count in the well like follows, the relationship works fine.

Where you might be getting confused is if you drop a field from Rqt_Ref into the values well and then you will get this error again and a full cross join. The reason for this is that you are asking Power Pivot to go from dimension to fact (OK) but then from fact to dimension (not OK)