PDA

View Full Version : can I create slicer connections to filter both pivot and regular tables?



joe1250
2018-02-02, 09:30 PM
Hi all, I'm pretty new at this and i'm expecting you to tell me the answer is no, but maybe someone knows a clever work around?

I have a large table and summary power Pivottable... Currently have slicers on the pivot table and would like them to also control the regular table, much in the same way that you can connect a slicer to multiple pivot tables, but I haven't been able to find anything on how to accomplish this... guessing it's not a built in feature?

If the drill down functionality was better i'd just use that after applying my slicer filters to generate the filtered list, but I keep getting the too many filters error...

Does anyone know if it's possible and if so how i can control a pivot table and standard table with a single slicer?

Thanks!

Ed Kelly
2018-02-09, 04:24 AM
Joe

No not possible can have separate slicers but not the same one controlling both (can only do that with multiple pivot tables). Not sure why you are having such problems with drill down, if need to clean the data would suggest doing it in Get data (Power Query) then pull into pivot table

joe1250
2018-02-09, 05:13 AM
Thanks Ed, the data should be clean... i am using power query to get and transform the data, then loading into the data model as well as a table in my workbook. All of the Power Pivot tables I've ever made have behaved the same, where if there are more than one or two filters applied, i get a message saying that excel can't drill down because there are too many filters... in this particular Pivot table i have 3 slicers connected and if chose more than one filter in any of them i get this error... I recall the drill down feature in classic pivot tables being a lot more robust in this respect. If you can shed any light on why i might be having these issues if others aren't, I'd very much appreciate it!

Thanks,
Joe

Ed Kelly
2018-02-10, 07:55 PM
Sam

It is totally possible consider send fake data same data structure and will take a quick look

joe1250
2018-02-14, 12:28 AM
Thanks Ed, I will try to assemble some fake data for you in the next couple days. The exact message i'm getting is:

"Show Details cannot be executed when multiple items are selected in a report filter field or in a slicer. Select a single item for each field in the report filter area and for each slicer connected to this PivotTable before performing a drill through."

Does it make any difference if my PivotTable connections are to queries vs. table from query vs. table in data model?

Would it be at all helpful to share my query code?

Thanks,
Joe

joe1250
2018-02-28, 04:57 PM
Hi Ed, I'm not sure what happened... I'd replied to the thread with a cleansed copy of my data, but i don't see that post here anywhere so I'm trying again...

The first link below is to a dummy copy of my source report, which my query transforms... you'll need to obviously update the source path if you want to see anything related to that part of the query...
https://www.dropbox.com/s/fvmg6uwkt9i5b78/Source%20Doc.xlsx?dl=0

The second link contains my pivot table and slicers that I can't seem to drill down and show details if I've selected multiple slicer filter values...
https://www.dropbox.com/s/76qa0638u22vcyp/Clean%20Test%20Tracking%20Doc.xlsx?dl=0

Thanks for your help!

Pecoflyer
2018-03-01, 08:40 AM
Hi
external links being potentially unsafe and not accessible to all members, please post your sheets on the forum so that everyone can access them ( click " Go advanced - Manage attachments). Thank you

joe1250
2018-03-01, 04:40 PM
appologies! i keep trying to attach them but keep getting the upload failed message...