Can't get one to one relationship - with related or by switching fields..

[abalserv]

hi Folks
I have a Powerpivot project with the following:
Chart of Accounts:
LinkID (unique field - text - text checked in Excel and Powerpivot)
Category
Classification
Company
Description

Company 1 Trial balance
LinkID (link for Chart of accounts) also checked to be text in Excel and Powerpivot. This is a concatenated field...
PTD Balance
YTD Balance

Company 2 Trial balance
LinkID (link for Chart of accounts) also checked to be text in Excel and Powerpivot.This is a concatenated field...
PTD Balance
YTD Balance

Company 3 Trial balance
LinkID (link for Chart of accounts) also checked to be text in Excel and Powerpivot. This is a concatenated field...
PTD Balance
YTD Balance

I have created the following relationships
Company 1 linked to Chart of Accounts (by Link ID)
Company 2 linked to Chart of Accounts (by Link ID)
Company 3 linked to Chart of Accounts (by Link ID)

The relationships all look fine. When I go to create a powerpivot combining these - I'm told "no relationship detected". What is particularly maddening about all this is that it did actually work at one point. WTF??
I have checked that there are no duplicates. I have checked that there are no blanks even though the slicer throws up blanks - I have gone through it line by line and can't find the blank it has detected.

At this point I'm thinking of going back to Excel and just creating a big table with lots of vlookups and doing a big pivot table instead...
Any suggestions would be most welcome. Thank you

 

[jafa1970]

Hi,

I do not have a direct answer, but perhaps trying the following may be of use:
- make sure the fields you are putting on the rows come from the Chart of Accounts table, not the TB tables
- test each one independently by removing the relationships and then adding back one at a time, testing in between to determine if the model still works
- use Power Query to combine the input tables into one (plenty of resources online showing how to do this)

If the above do not assist, perhaps provide more information as to the measures you have created, and what fields you are putting on the rows that result in the error.

Cheers
Phil

 

[abalserv]

Hi,

I do not have a direct answer, but perhaps trying the following may be of use:
- make sure the fields you are putting on the rows come from the Chart of Accounts table, not the TB tables
- test each one independently by removing the relationships and then adding back one at a time, testing in between to determine if the model still works
- use Power Query to combine the input tables into one (plenty of resources online showing how to do this)

If the above do not assist, perhaps provide more information as to the measures you have created, and what fields you are putting on the rows that result in the error.

Cheers
Phil

Hi Phil
Thanks for that. To reply to each of your queries..
1. The answer to the first question is yes, that's what I am doing...they come from chart of accounts
2. I've tried that and each of them work on their own but not when I create a relationship between each company and chart of accounts.
3. Looks as though I might have to do that but this is data from 3 different companies and each of them already have a way to present their data and I'm attempting to work with that.

Will check out the Power query option..

Thanks.
A

 

[PentaGalCXO]

When I have similar challenges with "no relationship detected" it is generally because I have broken one of the basic Star-Schema rules. Check the graphical view of your tables. Are all the arrows pointing in the right direction? Your example sounds like 3 fact tables (Company1, Company2, Company3) and a single dimension table (ChartOfAccounts), thus all arrows should point to ChartOfAccounts. For Measures I would define in each Company table a measure YTD_Cx:=sum([YTD Balance]), then a measure that sums them all YTD_Combined:=[YTD_C1]+[YTD_C2]+[YTD_C3]

When you build your Pivot Table ensure that only fields from ChartOfAccounts are in Row/Columns, and only the "Combined" measures in the Values section.

You didn't mention a Date dimension. With multiple fact tables you will most likely need one...

 

[abalserv]

When I have similar challenges with "no relationship detected" it is generally because I have broken one of the basic Star-Schema rules. Check the graphical view of your tables. Are all the arrows pointing in the right direction? Your example sounds like 3 fact tables (Company1, Company2, Company3) and a single dimension table (ChartOfAccounts), thus all arrows should point to ChartOfAccounts. For Measures I would define in each Company table a measure YTD_Cx:=sum([YTD Balance]), then a measure that sums them all YTD_Combined:=[YTD_C1]+[YTD_C2]+[YTD_C3]

When you build your Pivot Table ensure that only fields from ChartOfAccounts are in Row/Columns, and only the "Combined" measures in the Values section.

You didn't mention a Date dimension. With multiple fact tables you will most likely need one...

Thanks for this. Got all my arrows pointing the right way. and have done those measures as well. And my pivot is as suggested...any thing else?

 

[PentaGalCXO]

well... are you still getting the relationship error??? If so, perhaps you could share your workbook and I'll take a look...

 

[scottsen]

Make sure that whatever you are putting on rows/columns/slicers are coming from the lookup table... not 1 of the data tables.

 

[abalserv]

Had done that. Went back to basics. Rechecked all the linked fields. Turns out a few stray spaces had wandered in there...so got them all fixed and now it's working..thank you all