Drill Through Capability in Excel Pivot Table Lost

[EfRomero]

After upgrading Excel to version 2403 from 2402 the ability to drill through in pivot tables has been lost. When attempting to drill through the message "RETURN clause cannot be used with DRILLTHROUGH statement when a DetailRows expression is already defined on the measure...". There is no use of the DetailRows property in the model. This has occurred for myself and others.

Any help would be appreciated. Thanks.

[WJK]

We have the same problem with Office LTSC 2021, version 2108, build 14332.20685 (April 9, 2024).

Since I could still use both the old and new version of Excel, I traced the generated DAX-statements in DAX-Studio to compare them.

Below is an example of a generated statement by the new Excel, the blue part is new, this part was not generated by the old Excel.

DRILLTHROUGH MAXROWS 1000 SELECT FROM [Schendingen Care] WHERE (([Measures].[Aantal schendingen],[Periode].[Jaar-Maand].[Jaar].&[2024].&[December])) RETURN [$Schendingen].[Aantal],[$Schendingen].[Periodenr],[$Schendingen].[RoostergroepID],[$Schendingen].[Schendingsoort],[$Schendingen].[Schendingtype],[$Schendingen].[Schendingtype_key]

When you run this in DAX-studio it only gives the same error message if there is already a DetailRow defined in the tabular model. It looks like Excel is generating its own detailrow with all the columns from the base table. A quick fix is to remove the DetailRow from the tabular model, but that ofcourse is the last thing you want to do. It should be fixed in Excel.

[dipeshs1989]

Hi,

Anyone been able to fix the issue without rolling back on Excel version. We have multiple users affected here and administrator is unable to roll back to previous version.

Thanks

[natekistler]

We are seeing this issue as well. 

To add a bit more context to a possible solution. Version 2403 Build 16.0.17425.20124 is not impacted while Version 2403 Build 16.0.17425.20146 is.

[Ally_wu]

Hi,

Does the solution work. the RETURN clause is being added to drill through query when trying to drill through in Excel Pivot table connected with Power BI. Does any newer version of Excel handle the drillthrough differently?

[EfRomero]

The solution did work for me because I am an administrator on my machine. The solution will not work for those individuals that are not able to rollback the Excel version because they are not admins on their machine or their employer has a policy of not allowing version rollbacks. 

I am looking at other solutions that do not involve version rollbacks. I will continue this thread if I have positive results.

[dnspk]

Hi,

Were you able to find another solution for this issue that does not involve version rollbacks? A lot of folks in our organization are not admins on their laptops and version rollback is not a viable option.

Regards,

[cathryn13]

We had to roll back to a previous version.  We could not find a solution that worked without doing so.  Fortunately, we caught it with only one person and 2 developers that had the auto update install the latest version.  We turned off auto updates for the company to prevent this version from being applied to anyone else

[v-yifanw-msft · Accepted solution]

Hi @EfRomero ,

Based on the details you have provided, it appears that this issue may be related to changes or updates in Excel version 2403 that may affect the drill-down functionality in pivot tables. Below are steps to help you resolve the issue:

1. Sometimes, new updates reset or change settings. Double-check your Excel options, especially those related to data and pivot tables, to ensure that nothing has been changed that could affect drill-down.
2. If the problem persists and seriously affects your work, consider rolling back to a previous version (2402) as a temporary measure. A guide on how to revert to an earlier version is available:
   Latest updates for versions of Office that use Windows Installer (MSI) - Office release notes | Micr...
3. Since this issue affects multiple users, it may be helpful to check if it is a known issue in the Microsoft community or technical community. Sharing your experience there may also alert the Excel team to potential bugs introduced in the latest update.

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.