Two PowerPivot tables - with slicers

Question

Hi everyone,

I am very new to PowerPivot and don't even know if this is possible.  I have two tables displaying the same information; one by Month-End-Date and one by Pay-Period-End-Date.  I built identical slicers for each of the tables - just changing the date field.

My question is:
can the two sets of slicers be linked so the user only has to select the information on 1 set and it links to the other so both tables show the same departments or divisions at the same time? 
or
can both tables be linked to one set of slicers?

I know this is probably a really wild idea but it would be nice to be able to do it.

Thanks for any help you can give - or suggestions on other ways to display the data.
Thanks even if you tell me that my idea is not possible - then I'll stop getting frustrated by trying to make it work.

Tina

Answer 1

Hi Tina,

You can link the same slicer to two pivot tables through the slicer properties.

---

Boyan Penev --- http://www.bp-msbi.com

Answer 2

Boyan,

Thanks for the answer. 

Another question - I've been trying to find the 'Slicer Properties' - where do I find it?

• It's not in the 'Settings' area of the PowerPivot ribbon...
• I right-clicked on the border of the slicer area and it only showed: Object Positioning, Print Object, Locked and Lock Text. 

Thanks for your assistance,
Tina

Answer 3

Tina,

You can either:

1. Click on the slicer and then from the ribbon - Slicer Tools -> Pivot Table Connections select the pivot tables you want it active for

2. Click on the pivot table and then from the Insert Slicer itom on the ribbon select the second option -> Slicer Connections and select the slicers you want active for the pivot table

---

Boyan Penev --- http://www.bp-msbi.com

Answer 4

Thank you, it works just the way I had hoped.

Answer 5

Boyan,

I used your suggestion and two of my three pages are working great.  The cost and labor breakdown pages show the info by Month-End-Date and by Pay-Period-End-Date and break correctly when I click on the slicer.

Now I'm working on a page showing funding for the Current-Year and also for Inception-to-Date and I set it up the same way as the others but the slicers do not work - on either table.  When I click on the Current-Year table, the slicer area is highlighted (boxed) but if I select something, nothing happens - to either table.

Any suggestions on what I may have done wrong on this page?  I was setting the slicer up on the second table using the 'Insert Slicer > Slicer Connections' on the Options tab.

Thanks for your help,
Tina 

I just completely recreated the funding tab - new tables and new slicer on the 1st table - slicer works when one item selected.  I then added the slicer to the 2nd table and tried the slicer. The 1st table still works but the 2nd table stays as is - no change when a slicer item is selected.

Does that help narrow down my problem?  I'm hoping that I don't have to re-create this tab for the 5th or 6th time.

Thanks,
Tina

 

Answer 6

Hi Tina,

Sorry for the delay (just saw your last post).

Does it work when you create the second pivot table first (e.g. only that one) and add the slicer? If it does not work, what happens when you place the slicer attribute in the page filters area? Does it change the contents? My thinking is that something may be wrong with the relationships - e.g. the slicer does not affect the second table because there is no relationship defined..

---

Boyan Penev --- http://www.bp-msbi.com

Answer 7

Hi,

I cut the first PivotTable out of the Funding tab of the report and put it on a sheet at the end of the report.   I then set up the slicers on the second PivotTable and it worked as expected.  I then copied the first table from the back sheet put it back on the Funding tab and linked to the Funding slicers that were set up on the second table.  When I selected an item from the slicer, the second table worked fine but the first table just sat there - doing nothing!

But, I think I may have figured out part of the problem and I don't know if there is any way to solve it.  In order to get current month information and Inception-to-Date information, I had to create separate queries for each of the PivotTables - FundQuery and ITDQuery.  When I created the slicer on the second table, I was selecting the fields from the ITDQuery.Then when I linked the first PivotTable, the first table used the FundQuery fields so they wouldn't match the second table which used the ITDQuery fields. 

Is that the problem?  Is there any way around this?

Tina

Answer 8

Hi Tina,

 

It sounds like you do not have relationships defined between the two tables. Because of this, slicing the data in a slicer based on one of them will not affect the other one.

You can add a third table to PowerPivot adding just the attribute (distunct values) you are slicing by. Then you can create a relationship between the two tables and the attribute table. After that you can create a slicer from the new table and you will be able to slice both of the data tables together from the same slicer.

To illustrate, currently you are doing this:

 

Tables

TableA

TableB

(nor relationships)

 

Slicer

TableA(some column)

 

I am suggesting you try the following:

 

Tables

TableA

TableB

TableC (distinct values from some column in TableA)

(relationships: TableA <-> TableC and TableB <-> TableC)

 

Slicer

TableC

---

Boyan Penev --- http://www.bp-msbi.com

Answer 9

Boyan,

I corrected the relationships and it works like a charm.

Thanks for all your help,
Tina