It has been said there is no way to change the default drill through behavior in PowerPivot. However, if we look closely we discover that we can actually exercise a bit more flexibility than normally assumed when using this feature.
The first thing to understand is that drill through retrieves data from whichever table the measure has been assigned to.
This is an interesting feature – yet, not very intuitive –: as it is possible to assign a DAX measure to any table in the model (even disconnected ones) without affecting the output of the measure at all, one can allocate measures to tables specifically designed for drill through.
Lets look at an example using Adventure Works.
[Sum of SalesAmount] is an implicit measure. As such, it was allocated to the table with numeric values (FactResellerSales). Notice this table is on the many-side of the context propagation. When executing a drill through, we get an ‘exploded’ view of that table showing raw data:
Also notice that many technical columns are retrieved, which may not provide any value to the analyst (ResellerKey, for example) . Instead, lets create a custom table to support a more customized drill through view, with only the columns we are interested in. I will use the Table Import Wizard instead of writing straight SQL, as I believe this is something analysts will be able to configure on their own without the need to code:
By specifying the use of ‘Group and Aggregate’, we will get a list of the distinct product and order date keys in our fact table. As we might not interested in the original granularity as we are simply supporting a drill through action in order to show what dimension members make up a specific sales amount, a list of product and date members is sufficient in this case.
We can source the desired columns from the lookup tables by using RELATED. Or, to save memory space or for model usability, you may decide not to bring those columns on the visible lookup tables and only retrieved them from the source to populate the custom drill through table.
All columns should be hidden (do not hide the table, only the columns. Hiding the table will hide the measure we will assign to it which is not what we want).
An explicit DAX measure is created at this point:
[Sum of Sales]:=SUM( FactResellerSales[SalesAmount] )
and assigned to the ‘drill through sales’ table:
As only the measure is visible on this table, the pivot table field list will not show the regular table icon but one indicating the summation sign:
When clicking on the cell we clicked before, this time we get a more ‘customized’ drill through:
Note the keys connecting to the related tables are hidden, they are never retrieved on the drill through view. This is good, as they are technical and not needed for analysis.
The order of the columns depends on the order in which you added the RELATED columns on your custom drill through table. Simply moving the column to another position won’t change it on the drill through view. To change order, it is necessary to re-create the order you wish to see.
In order to custom drill through views to work, the default context propagation (from the one-side to the many-side) must be respected. As such, if you want to provide a small drill through experience from the visible lookup table you should snowflake the model instead of connecting both tables (visible lookup and custom drill through) to the fact table separately:
Note: in Excel 2013, there appears to be no way to change the default behavior of 1000 rows retrieved (this was not the case in Excel 2010).
Javier Guillén 