Forum Discussion
Analyze in Excel can't see underlying data
Hello,
I created a star schema and uploaded it to Power Bi services. then downloaded the data using "Analyze in Excel".
When I double-click a value in the Excel Pivot Table (from Analyze in Excel), the underlying data does not show. I want to know what settings or changes are required in Power BI so that users can see the detailed underlying data when they double-click an amount in the pivot table.
Hii makshata96
In Analyze in Excel, a PivotTable is connected to the Power BI semantic model, so it cannot automatically show the underlying rows for a DAX measure. To enable drill-through (double-click), you must define a Detail Rows Expression for that measure in Power BI Desktop (Model view >> select measure >> Detail rows expression). Without this, Excel will always return blank because it doesn’t know which table to pull the detailed rows from.
6 Replies
rohit1991Super User
Hii makshata96
In Analyze in Excel, a PivotTable is connected to the Power BI semantic model, so it cannot automatically show the underlying rows for a DAX measure. To enable drill-through (double-click), you must define a Detail Rows Expression for that measure in Power BI Desktop (Model view >> select measure >> Detail rows expression). Without this, Excel will always return blank because it doesn’t know which table to pull the detailed rows from.
Thanks, rohit1991, but it’s still not working. Even if I publish a single table without any DAX expressions and then use Analyze in Excel, the PivotTable still does not show the underlying data when I double-click the value. It looks like drill-through is not supported in this scenario.
- rohit1991
Hii makshata96
Detail Rows Expressions work inside Power BI, but Excel uses an OLAP connection when you choose Analyze in Excel. Because of that, drill-through only works in very limited cases, and the error you are seeing (“subselects and subcubes…”) means your dataset falls into the scenarios where Excel cannot retrieve underlying rows.
So the model is working correctly it’s Excel that does not support detail row drill-through for this type of dataset.
Thanks for the explanation. I understand now that the model is working correctly and the limitation is on the Excel side. However, I still need a solution for this issue, because users want to view the underlying data when they double-click in the PivotTable. If there is any supported method or alternative approach, please let me know.
