Power Pivot - Help with multi-table relationship

[Evans2]

Hi all,

I'm having some trouble with a multi-table relationship and hoping for some help. I know what I'm trying to do is possible but I'm just going about it the wrong way.

I've got 5 tables I'm working with. PORDER with a P# (part number) that is related to PO_SO with P# and S# (sales number) that is related to SORDER that has the S# and Customer Code # related to CUSTOMER with the Customer Code # and unique Customer Name and finally my main SALES table with Customer Name and S#. My ultimate goal is to be able to filter by Customer Name, S# (sales number) and the P# (part number) associated with each sales number.

any help is super appreciated. My table relationships detailed below. Thank you

---

[TABLE="class: outer_border, width: 90"]
<tbody>[TR]
[TD]PORDER P(P#)[/TD]
[/TR]
</tbody>[/TABLE]
↑

[TABLE="class: outer_border, width: 100"]
<tbody>[TR]
[TD]PO_SO (P# & S#)[/TD]
[/TR]
</tbody>[/TABLE]
↓

[TABLE="class: outer_border, width: 100"]
<tbody>[TR]
[TD]SORDER (S# & Customer Code)[/TD]
[/TR]
</tbody>[/TABLE]
↓
[TABLE="class: outer_border, width: 100"]
<tbody>[TR]
[TD]CUSTOMER (Customer Code & Unique Customer Name)[/TD]
[/TR]
</tbody>[/TABLE]
↑
[TABLE="class: outer_border, width: 100"]
<tbody>[TR]
[TD]SALES (Customer name & S#)[/TD]
[/TR]
</tbody>[/TABLE]

 

[scottsen]

When you create relationships, Power Pivot will generally just... create them correctly (detect the many side, and one side... of the 1 to many relationships). It only gets interesting if you have 1:1 or Many:Many relationships. Do you have any of those?

The only other thing of note is that you should generally be using your "lookup tables" (the 1 side of the 1 to many) on filters (rows, columns, slicers, dax expressions).

Any specific problem you are having?

 

[Evans2]

Hi scottsen,

Thanks for responding. I just have one to many relationships. No problems creating the relationships between the tables. The problem I'm having is that no matter what I do, I'm not able to filter by Customer Name, S# and P# together. I get a "Relationship needed" error. If I filter by my Customer Name lookup table and the related SORDER table containing the S# then the related PO_SO table containing the P# I would think that should work but Excel just flips out and crashes on me.

 

[scottsen]

Define "flips out and crashes" Like... literally bye-bye excel process!?

What version of Excel?