PowerPivot not working as intended?

Hi, I seem to have this problem in Excel Pro Plus 2016. Well, I have this problem too for Excel 2013.

I think this is probably something simple that I'm missing and I hope you can help me.

I can't seemed to make the PowerPivot I've created reflect the many to many relationships correctly. Please have a look at the attached file and the steps I've written:

1. Create lists of unique values - Buildings, Teams and Consultants
2. Create relationship tables - Buildings-Teams and Teams-Consultants
3. Added all the above to Data Model
4. Used Diagram View to create relationships - mapped IDs to IDs in the tables
5. Created PIVOT


Problem:
1. "Relationships between tables may be needed" keep appearing even though I've already created in step 2 above.


2. PIVOT doesn't reflect what was expected ie. the relationships implied in the relationship tables (step 2 above). It seems to just populate every single Team-Consultant relation (which is correct btw) to each Building which is contrary to what is proposed in the Buildings-Team relationship table.


Please please help - thank you very much!!


Btw, just realised that I can't attach a file? Clicking the attachments button above brings up an empty box with no words/letters?

Rgds,
Michael

Originally Posted by fingerstyle

I can't seemed to make the PowerPivot I've created reflect the many to many relationships correctly.

I see something different

rel.jpg

Would be fine to see result (mock up manually if necessary)

maybe this one is what you want:

Could you tell me any logical reason why it should be like in your last file?
You've wrong relationship: Buildings Team (many) to Buildings (one) and the same table Buildings Team (many) to Team (one)
Table Buildings Team should have any unique ID (no duplicates) but it doesn't.
Look at table Team Consultant: Consultant ID (unique) and Team ID (duplicated) and it is ok.

First what you need is build correct relationships then play with PivotTable but not vice versa

Maybe read here: Relationships between tables in a Data Model or here: Relationships Overview

cheers

You can analyze this file and change what you want but must remeber about unique (primary) key for each table
and don't imagine PT but first build the correct relationship. If rels will be incorrect you will see warning in PT of course

So add three different CIDs for the same person then define TID and BID
You can define one TID and two BID or whatever but if one person (Lauren) should have access to three different things she should have three different CIDs
You can play with TIDs and BIDs in the rels table but CIDs should stay 3 different.

and remember , we are talking about example not about your real data

here is example

I think this is what you want

You can define Teams/Buildings/Consultants as you wish but in TBCID table ID must be unique {!}. (unfortunately 1st header and name of table are the same: TBCID but I'm too lazy to change it )
If you do something wrong with TID/BID or CID in this table you'll see blank or red in PT (eg. duplicate rows values)
Associate names to CIDs in TBCID table is not necessary

morning is more wise than evening