Forum Discussion

visu-l's avatar
visu-l
Copper Contributor
May 03, 2024

Drill Through Capability in Excel Pivot Table lost

When calling the details function on a pivot table (source is an OLAP-cube on SSAS) I am no more able to drill through. I got this error message:

RETURN clause cannot be used with DRILLTHROUGH statement when a DetailRows expression is already defined on the measure..

There is a similar https://community.fabric.microsoft.com/t5/Service/Drill-Through-Capability-in-Excel-Pivot-Table-Lost/m-p/3805877 on the fabric community. However I do not query a Power BI cube but a SSAS cube (which is - from Excel as client - similar indeed). There is a DetailRows expression defined in the cube; it was working up to the Office 365 MSO Version 2402.

Anyone who encounter same issue and/or find a workaround without rolling back to a previous version?

Best regards

Emmanuel

BI & Data Analysis
excel
office 365

1 Reply

  • AshaKantaSharma's avatar
    AshaKantaSharma
    Iron Contributor
    Aug 20, 2024
    The issue with drill-through functionality in Excel PivotTables when querying an SSAS cube is related to the interaction between the `DRILLTHROUGH` statement and the `DetailRows` expression. To resolve this, check the cube definitions, update metadata, modify the DetailRows expression, consult SSAS documentation, adjust PivotTable settings, clear cache, verify compatibility, use an alternative method like SQL Server Management Studio (SSMS), or contact Microsoft Support for further guidance.

Resources