Link PowerPivot slicer to pivot tables based on different data sources

0
votes

I'm new to PowerPivot and have been asked to link multiple pivot tables pulling from multiple PowerPivot data sources.

Looking at blog posts and forum discussions it seems to be possible to either link based on a one to one or one to many relationship and also - if following the recommended models - linking via many to many relationships.

The data sources have primary/foreign key fields which I have used to create a relationship within PowerPivot.

I have used PivotTable Connections to link a slicer to pivot tables from both data sources. But, when I select a value in the slicer, only the initial pivot table (and pivot tables based on the initial data source) are updated.

Can anyone point me in the right direction to link the pivot tables?

I'm using Excel 2010 and PowerPivot (Version 10.50.4000.0)

The data sources are two separate Access Queries.

Thanks

Mark

Update: Well, as you do, I've kept working on this. I worked through the suggestions here http://www.powerpivotpro.com/2010/08/two-common-powerpivot-error-messages/ in case the linking of the slicer to pivot tables wasn't registering somehow. Maybe this helped, I'm not entirely sure. After following all the suggestions, none of the slicers I'd created were affecting pivot tables based on the other data source.

As part of the above suggestions I created a calculated field on qry_Table1 (not real table names...) : 

=CALCULATE(COUNTROWS(qry_Table2),qry_Table1)

I wondered if there was an issue with the slicers having been created before I'd created the relationships so I deleted most of them (There are actually a lot of pivot tables and sheet tabs and slicers on each tab and I missed a few)

Then I tried creating a stand alone slicer and linking that to the relevant pivot tables but no joy.

Then I went back to creating a slicer from a pivot table whose data source is qry_Table2. Clicking a cell in a pivot table drawing from qry_Table2 then from PivotTable Tools > Options tab > Insert Slicer. I created the slicer and again linked it to pivot tables from both data sources. This time it worked.

I have to confess I don't understand why. It may be to do with the direction of the relationship qry_Table2 is the lookup table. It may be to do with the DAX formula.

It is probably worth saying the slicer was on a kind of Company Department column which is not joined on, is not referred to in the DAX formula.

Anyway, I'm going to try and replicate this.

1
excel-2010powerpivotslicers

1 Answers

0
votes

Copying the slicer that worked (and creating new slicers off the same pivot table), I've been able to allow users to select department and other dimensions on each sheet tab and have it update all pivot tables regardless of data source.

I decided to delete all existing slicers and start again, working from the pivot table pulling from the lookup table as defined in the relationship. I've since tested these new slicers and they are also updating all linked pivot tables regardless of data source.

The following articles/whitepapers have also been useful as I've been investigating this (though I haven't created a many to many relationship):

http://www.powerpivotpro.com/2012/11/a-mystifying-and-awesome-solution-for-many-2-many/

http://www.sqlbi.com/articles/many2many/

It is also possible the advice given in the link on the original post re error messages was also helpful.