-Polak
asked on
Workarounds for PivotTable Cannot ShowDetails When Multiple Filters Are Applied
I am receiving this error:My 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.
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
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.