Results 1 to 8 of 8

Thread: can I create slicer connections to filter both pivot and regular tables?

  1. #1
    Seeker joe1250's Avatar
    Join Date
    Feb 2018
    Posts
    14
    Articles
    0
    Excel Version
    2016

    can I create slicer connections to filter both pivot and regular tables?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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!

  2. #2
    Conjurer Ed Kelly's Avatar
    Join Date
    Jul 2016
    Posts
    162
    Articles
    0
    Excel Version
    2016
    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

  3. #3
    Seeker joe1250's Avatar
    Join Date
    Feb 2018
    Posts
    14
    Articles
    0
    Excel Version
    2016
    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

  4. #4
    Conjurer Ed Kelly's Avatar
    Join Date
    Jul 2016
    Posts
    162
    Articles
    0
    Excel Version
    2016
    Sam

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

  5. #5
    Seeker joe1250's Avatar
    Join Date
    Feb 2018
    Posts
    14
    Articles
    0
    Excel Version
    2016
    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

  6. #6
    Seeker joe1250's Avatar
    Join Date
    Feb 2018
    Posts
    14
    Articles
    0
    Excel Version
    2016
    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/fvmg6uwkt9...0Doc.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/76qa0638u2...0Doc.xlsx?dl=0

    Thanks for your help!

  7. #7
    Administrator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,666
    Articles
    0
    Excel Version
    2010 on Xubuntu
    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
    Thank you Ken for this secure forum.

  8. #8
    Seeker joe1250's Avatar
    Join Date
    Feb 2018
    Posts
    14
    Articles
    0
    Excel Version
    2016
    appologies! i keep trying to attach them but keep getting the upload failed message...

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •