2

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 : enter image description here

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 : enter image description here

When I then create a pivot table, the relation has not impact : enter image description here

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 : enter image description here enter image description here enter image description here

Alexandre Rivara
  • 113
  • 1
  • 11

1 Answers1

1

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

enter image description here

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.

enter image description here

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

enter image description here enter image description here

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) enter image description here enter image description here

Davide Bacci
  • 16,647
  • 3
  • 10
  • 36
  • I fixed the relation, I went too fast building this example, thank you. What is disturbing is why would the join works in one direction and not the other ? In my mind, the type of join set the way the link is made but not direction it is read ? Am I just wrong and should go read my sql lessons again ? – Alexandre Rivara Jun 26 '22 at 17:45
  • Direction goes from one to many if you just use relationship for the logic. You will need DAX to go in the other direction. Don't forget to mark as solved if your questions is answered to your satisfaction. – Davide Bacci Jun 26 '22 at 17:55
  • I understand the limit I am facing but I don't understand why it's there. What's the mechanism behind it ? Is there a situation where this is useful ? Once the join is in place it should be possible to read it in both direction as when the table is built with Power Query merge tool, no ? – Alexandre Rivara Jun 26 '22 at 18:30
  • I feel your frustration but relationships in Power Pivot do not work that way. They always filter in the direction of the arrow from one-to-many. Power BI allows bi-directional filters but these bring their own problems with model ambiguity and performance. You are supposed to model a star schema with dimensions on the outside filtering your fact table on the inside and the software has been designed with this in mind. – Davide Bacci Jun 26 '22 at 18:46
  • 1
    Ok, I guess I will just accept it. I edited the question to add the manipulation through Power Query merge queries. In the end, it's not a bad solution in my humble opinion but yes, that frustrate me =) Thank you for the explanations ! – Alexandre Rivara Jun 26 '22 at 19:19