We will explain how the drillthrough action works in Excel then, we will see how to customize the Detail Rows Expression property to control columns and rows returned by the drillthrough action. This article explains how to use the Detail Rows Expression property to customize the Excel drillthrough behavior for a PivotTable connected to a Power BI dataset or an Analysis Services database – they can be identified as Tabular models in the remaining part of this article. Through the Detail Rows Expression property of a measure in the Tabular model, you can customize the drillthrough behavior in Excel, thus controlling the rows and columns returned to the user. In many scenarios, this default behavior does not provide a result consistent with the data computed in the result that you see visible. It returns all the rows visible through the filter context in the table that includes the measure definition. While the customization of actions is not feasible for a Tabular model, the drillthrough feature is active by default. In a Multidimensional model, it is also possible to add different drillthrough actions that can be activated through the context menu in Excel. This feature was initially designed for Multidimensional databases in Analysis Services. When you double-click on a cell in an Excel PivotTable, you invoke the drillthrough feature of the PivotTable which shows the underlying data for that particular cell.

Office 365 excel customize ribbon