PowerPivot and many to many relationships not working

[fingerstyle]

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.


<attachment></attachment>
Please please help - thank you very much!!

Rgds,
Michael

 

[fingerstyle]

Hi, how can I attach a file to my post above?

 

[RoryA]

You can't I'm afraid. You'd have to upload it somewhere (e.g. OneDrive / Dropbox / other host) and post a link back here.

 

[fingerstyle]

Apologies - here is the link:

https://1drv.ms/x/s!ArUIP18pEKAfgQLZU46vPjrsljkT

Thank you!!!