Excel slicer not working in two pivot tables

Question

_{Monday, May 9, 2016 6:35 PM}

I am creating some dashboards based on Project Online. I can acess OData and pull the tables into Excel 2016 just fine by using Data-Get external data-Odata or by using Data-New query-From Odata.

My problem is that when I create two pivot tables, one containing Projects and a second one with Tasks, even if my tables are related, when I create a slicer on either pivot table, it just filters the original table, not both.

I have verified several times the relation between tables. Both ProjectID and ProjectName exists on both tables but no matter how I create them, I cannot find how to use a single slicer to filter my two pivot tables.

Am I missing something?

Rene Alvarez

All replies (3)

_{Tuesday, May 10, 2016 12:20 PM}

Hi Rene Alvarez,

Based on your description, first please make sure you're using the same data source for each PivotTables in your environment.

Then you need to create Report Connections for Slicer, please right click the Slicer and select Report Connections.

Please make sure you have checked the PivotTables you need to filter.

You can refer to this link get more information about your problem:

https://exceljet.net/tips/how-to-use-a-slicer-for-multiple-pivot-tables

Please Note: Since the web site is not hosted by Microsoft, the link may change without notice. Microsoft does not guarantee the accuracy of this information.

Any updates please let me know and I'm glad to help you.

Regards,

Emi Zhang
TechNet Community Support

Please mark the reply as an answer if you find it is helpful.

If you have feedback for TechNet Support, contact [email protected].

_{Thursday, June 2, 2016 8:29 PM}

Emi,

Sorry for the delay and thanks in advance for your response.

I'm not sure this is the answer since "the same data source" can mean different things. It's the same data source since all my data is in the data model in the very same Excel file, however they come from different tables.

I cannot believe that I need a join and duplicate hundreds (or thousands) of records creating a huge table that contains everything in order to connect the slicer, that's just not a practical solution.

Am I missing something?

Rene Alvarez

_{Monday, January 28, 2019 5:44 PM}

Hi Rene,

this looks like a bug to me. I checked it and found Project Online exercises with two or more tables (e.g. Projects and Tasks) that I created some years ago, which are filtered with a slicer. They still work, but if I re-do the steps, it is not possible anymore. Seems to me related to Excel, rather than Project Online. In Power BI it works perfectly. Alternatively, you could e.g. merge both queries.

Best regards,

Julian, PMP, MCP, MCSA, MCSE

Blog: https://project-brain.com

Note: Posts are provided “AS IS” without warranty of any kind, either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.