PowerPivot not always linking tables correctly

[ItPaysTheBills]

Hello all. I'm using Excel 2013 pro version.

I've built several slicer controlled dashboards linked to pivot tables based on data in multiple tables in power pivot. Most often, the powerpivot data is linked to a SQL Server database or an OBDC database.

I'm linking tables in a star schema to our general ledger data table. Most of my tables seem to link fine. For instance, there might be "BUDGET CODE" in the ledger datatable and I have it linked to another data table that has the description for each BUDGET CODE (linked with the common BUDGET CODE, in a one to many relationship) and I slice with the budget code description without a problem.

On others, it doesn't work and I really can't explain why. Let's say I have two tables, a customer account data table with unique customers accounts and another with say refund check numbers. So there is a one to many relationship, and I can successfully link (in diagram view of PowerPivot) the customer account datatable to the customer checks datatable (one to many relationship). Now, lets say I have a pivot table with customer account is row position 1 and I place refund check number in row position 2. It bogs, and finally asks me if I want to show as many rows as possible (over a million, filling the sheet row limit). The result will be each customer having every single refund check we've ever written underneath it in the pivot table. In other words, it's apparently not correctly linking "customer account" between the tables. The field list window of the pivot table is warning that some links may need to be created. When I click the link it shows the link between the two tables already established.

I've evaluated the customer accounts to be equivalent. When I copy into a sheet I can find that account "12345-98" equals "12345-98", so there don't seem to be any blanks embedded preventing a proper link. Both columns that are being linked are formatted the same in PowerPivot, in case that matters. I've attempted a lookup with the "Related()" DAX function and receive an error as well.

This problem seems to happen on many of my data table links, and I can't see why.

Any ideas?

 

[scottsen]

Sounds like an issue with your table relationships, but would need to see the workbook. If its not uber-sensitive you could send a link to the file on dropbox or google drive?

 

[ItPaysTheBills]

Sounds like an issue with your table relationships, but would need to see the workbook. If its not uber-sensitive you could send a link to the file on dropbox or google drive?

Thanks for the reply Scott. Unfortunately, every workbook with this issue has sensitive information that I can't really distribute. I hate to not help you help me, but I just can't share them.

Perhaps naively, I was under the impression that if it let you link it than the relationship must be "good." Are there any particular relationship problems I should be looking for? There is a one to many relationship, and the "primary keys" have been found to be of the same structure. I know for a fact there are matches that Excel evaluates to be equivalent.

 

[scottsen]

Can you add a screen shot of your relationships? That will probably help the discussion.

 

[ItPaysTheBills]

Can you add a screen shot of your relationships? That will probably help the discussion.

Scott,

Thanks for your help with this. Here is a snapshot of the relationships in the most simple workbook that has this problem. There are only three tables that I'm linking in this one. All of them link on a "member-sep" field. This calculated field is called something different in each table, and I don't even think its visible in the middle table (oops), but I have evaluated them to be equivalent.

<a href='https://dumpyourphoto.com/photo/xo0rDF02Hw'>https://dyp.im/xo0rDF02Hw</a>
Now that I look at it a bit more myself, does it have anything to do with the direction of the link. I see that the arrow starts with a bold circle and then "points" to the related table with an error. The table on the left contains many instances of the same "membsep" while the middle table has unique instances. If I dragged from the middle to the left, would it make any difference?

Thanks

 

[scottsen]

The direction of the relationship does matter, but powerpivot tends to be pretty good about getting the direction correct. It always "points" from the "many side" to the "one side". (And if its many to many, just blows up.... and if one-to-one... that's the only case where YOU have to be careful, cuz powerpivot has no idea which is the "lookup" table in that scenario).

Anyway, the arrows always "point" to the lookup table.

The other thing I will say is that 99.99% of the time, the fields you want to drop on rows, will come from your lookup table (as opposed to the fact table).

The last thing I will throw out there (in hopes of totally randomly solving your problems without actually knowing what is going on ) is that you want to think of these relationships in terms filtering. If I had a budget table and a sales table... they might both be linked to a territory lookup table (or calendar table, etc). This is because I want to filter rows in those fact tables by a certain region (or year, etc). But I probably DON'T have a relationship between budget and sales. My budget doesn't really "filter" my sales, or visa-versa (in reality, this would be a blowup on the many to many anyway).

Your relationships strike me as maybe possibly potentially odd in that the SAME id is used to relate all 3 tables? That feels atypical. A typical chain might be a InvoiceId relating to invoice table, and maybe that relates to a region table by RegionId or calendar via Date or whatever. But the SAME key between 3 tables... possible, but strikes me as odd.

 

[tjdurkin]

IPTB,

Are you using fields from both CheckDetail and MbrSepMaster in your pivot? If so, I believe PowerPivot is confused on how to handle since there is no direct relationship between those tables (ie, there is an indirect relationship that runs through the MemberDetail table). If you need to use an attribute from the MbrSepMaster table, create a calculated column in MemberDetail and use =related(MbrSepMaster[fieldnameyouneed]) to bring that information into MemberDetail. Use this calculated column in your pivot instead of the field that you are using from MbrSepMaster. The pivot should organize the information as you expect if my assumption is correct.