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. This feature was initially designed for Multidimensional databases in Analysis Services. In a Multidimensional model, it is also possible to add different drillthrough actions that can be activated through the context menu in Excel. While the customization of actions is not feasible for a Tabular model, the drillthrough feature is active by default. It returns all the rows visible through the filter context in the table that includes the measure definition. In many scenarios, this default behavior does not provide a result consistent with the data computed in the result that you see visible. 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.
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.
Read on for a demonstration, plenty of explanation, and even some elucidation.