Workarounds for PivotTable Cannot ShowDetails When Multiple Filters Are Applied

I am receiving this error:Capture.JPGMy Situation (I'm building a dashboard and analytical tool):
1. Using PowerPivot to Import Data into the DataModel
2. Cleaning the data and creating calculated columns in a linked EXCEL table* then sending the data back into PowerPivot as a "TableCleanData"**
3. Building my PivotTables and Pivot Charts using Pivots whose data source is TableCleanData
4. Connecting slicers to the fields in TableCleanData to filter my Pivots

I was unaware that I wouldn't be able to view/drilldown/showdetails on Pivot's Data after more than one slicer/filter was applied. I'm looking for a workaround. I can think of one in my head, but I'm not sure its best. If it is the best I'll still need help with the VBA.

Workaround A: Base my Pivots off of my linked Excel table instead of the "CleanDataTable" in PP's DataModel. Connect the Slicers to the Excel Table (not the Pivots) then call a Macro to update all of the Pivots any time a slicer is executed against the Excel Table. This way if I ever want to view the data being displayed I would just have to go to a worksheet where the linked table exists and the table would be filtered by the slicers.  

Workaround B: ???? EDIT: not sure if this is possible, but some sort of Slicer linking/synchronization; ala Slicer A that is connected to TableCleanData in PP syncs its filtering options with Slicer B which is connected to the Excel Table.

*I am cleaning in Excel for a several reasons, I do not know DAX very well, and because I'm using custom functions written in VBA to clean the Data, which as far as I know I can't convert for use in PP's DataModel
**If it makes a difference the data consists of 27,000 cells, about 1/2 of those are calculated columns or use the aforementioned VBA.
LVL 1
-PolakAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

-PolakAuthor Commented:
For anyone experiencing the same error while trying to ShowDetails on a PivotTable/Chart connected to an OLAP data source after a great degree of difficulty I went with Workaround B.

Basically, I pass any slicer selections made on the OLAP datasource to identical slicers connected to a regular Excel Table. On my Pivot Charts I place a icon that looks like a Magnetic Disk where user's can click to "Get Data". The Icon simply activates the worksheet where the regular excel table resides and has been filtered by the connected non-OLAP slicers. For the code to pass selections made on an OLAP slicer to Non-OLAP slicers I described that struggle in this question: Linking Slicers with different Data Sources
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.