powerpivot relationships not detected

agwalsh
agwalsh used Ask the Experts™
on
hi Folks
I've got a master chart of accounts list - unique identifier called LinkID. I have 3 sets of trial balances each of which has a unique field called LinkID as well. Each of these linkIDs has a balance attached to it. e.g. Retail sales PTD and Retail Sales YTD.
So I have a one to one relationship, as opposed to a many to one relationship. I have tried creating these relationships both ways and in both cases when I go to add the values i.e. the YTD and PTD values to the powerpivot I am told "no relationship is detected". The Related function does not work to allow me to pull in matching values - so that even though it looks as though the relationships are working...they are not. Any suggestions?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Director, Practice Manager and Computing Consultant
Awarded 2014
Top Expert 2014
Commented:
>> even though it looks as though the relationships are working...they are not

Could you please elaborate on how they look like there are relationships. Some screenshots would help.

Have you gone through the procedure here: https://support.office.com/en-gb/article/Create-relationships-in-Diagram-View-in-Power-Pivot-12e00cb6-cb4d-469c-97ce-caa08349ad76?ui=en-US&rs=en-GB&ad=GB

Do you have more than one relationship per set of tables? (as PowerPivot will only use one). If so, use USERELATIONSHIP.

Author

Commented:
I can see them in the Relationship window (Diagram View) going from Company 1 to Chart of accounts, going from Company 2 to Chart of accounts, ditto for company 3. All linked on LinkID field. No, just one relationship per table...so I create them, they appear in the Diagram window. I now have one of the company files relationships working...so still have two to go..
Phillip BurtonDirector, Practice Manager and Computing Consultant
Awarded 2014
Top Expert 2014

Commented:
As I said, some screenshots would help, as I'm a bit in the dark.

Author

Commented:
Thanks again for your help...I got everything working - problem with data types not matching, spaces where there shouldn't be...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial