Forum Discussion
Problem with Drill-down pivot table
Hello Fredrik,
have you found a solution yet?
I am facing the same problem.
I've created a dashboard with many data slicers and an option to drill down the filtered data.
If there is a data slicer with multiple selections and any other data slicers with at least one filter, then the drill down ("show details" / data extract) won't show the correct number of entries. Some data slicers will be just ignored. It's very confusing.
This only happens when the data comes from the power pivot data model. I've created the same dashboard having the complete data in a work sheet. There it works without any problems. I only get the filtered data back when I drill down, no matter how I set the data slicers.
This seems to be a bug to me. In Excel 2013 it wasn't possible to select multiple values within a data slicer and afterwards to drill down. Finally it works in Excel 2016, but it's a really weird bug, that it doesn't work correctly when the data comes from the Power Pivot data model.
Best regards,
Virty
Hi Virty
No, I gave up and created the same dashboard with pivottables and slicers from a regular Excel table instead. For my needs it wasn´t really necessary with a Powerpivot table I only wanted the option to create measures in an easier way.
It really looks a bug to me too, at least I haven´t found any good reason why this functionality would differ Power Pivot models/regular excel tables. Because one thing is clear, it does differ.
Best regards
Fredrik
Hello Fredrik,
thanks for your fast reply.
I am also sure, that this must be a bug.
It's a pity that I have to create my dashboard based on a regular worksheet now. It increases the file size by several times and also my measures don't work anymore, so I have to use some work-arounds based on normal pivot tables.
Best regards,
Virty
- I'm pretty sure this is not a bug but rather a design problem in your model. FIlters MUST be set on fields containing JUST the filter values. For example: Suppose your data contains a column with (possibly repeating) date values. You should never filter your pivots on that column (field). Instead, you should be adding a date table which contains all dates (NO DUPLICATES!) you have in your model, create a link between the date and the data table and then filter your model using the date field of the dates table.
Hello Jan Karel,
I have tried what you've said and it's still not working. I still get the wrong amount of results back when I drill down having multiple selections within one or more data slicers and in one data slicer only one selection. So it's not a design problem. Seems to be a real bug.
So to make it clear:
I set select one item in any/all data slicers --> No problem.
I select 2 or more items in any/all data slicers --> No problem.
I select 2 or more items in any data slicers, but in one data slicer I select only 1 item --> Wrong amount of data sets in the drill down!
Best regards,
Virty
