Forum Discussion

EfRomero's avatar
EfRomero
New Member
2 years ago
Solved

Drill Through Capability in Excel Pivot Table Lost

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 DRILLTHROUG...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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 | Microsoft Learn
    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.

     

WJK's avatar
WJK
New Member
2 years ago

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.