Function Problems

Fix Excel Cube Function Drill-Through Not Working to Power BI Details

John WilsonJohn Wilson Nov 13, 2026 869 views

Question details

The user needs to understand why drill-through works in an Excel PivotTable but not from cells containing CUBEVALUE or other cube functions, and how to access Power BI transaction details.

How to Fix Excel Cube Function Drill-Through to Power BI Details
Product
Microsoft Excel with Power BI or Analysis Services cube functions
Device & OS
not provided
Scenario
The user is analyzing an Excel workbook connected to a Power BI dataset or Analysis Services cube and needs transaction-level details by clicking a cell.
Observed behavior
Drill-through succeeds from a PivotTable but is unavailable or does nothing from cells that contain CUBEVALUE, CUBEMEMBER, or other cube functions.
Before you start

Confirm the workbook is connected to the same Power BI dataset or Analysis Services model and that drill-through works from the original PivotTable. Also identify whether the selected cell contains a cube formula, because this determines which workaround applies.

Solution 1Recommended

Use the Source Excel PivotTable for Native Power BI Drill-Through

Best when you need the fastest native way to view transaction details without rebuilding the report or changing the workbook layout.

Cube formulas return values but do not carry the row/column member context that Excel drill-through requires. The original PivotTable retains that context, so it remains the most reliable route to transaction-level detail.

1
Locate the source PivotTable

Open the Excel workbook and click any value cell inside the PivotTable that is connected to the Power BI dataset or Analysis Services cube.

2
Run Show Details

Right-click the selected value and choose Show Details from the context menu, or double-click the value. Excel creates a new worksheet containing the matching transaction rows.

3
Review the detail sheet

Check the newly created sheet for the underlying records, then format, filter, or export it as needed.

Use the Source Excel PivotTable for Native Power BI Drill-Through
Native drill-through path restored: After using the source PivotTable, Excel creates a new worksheet with the detail rows from the connected Power BI model.
Free Microsoft Office alternative

Use WPS Office as a Free, Lightweight Excel-Compatible Alternative

The drill-through limitation is part of Microsoft Excel's Power BI data model behavior, not an issue WPS Office can change. If you mainly need to open, edit, and share Excel workbooks with cube formulas and PivotTables, WPS Office is a free, lightweight alternative with familiar ribbon tools and strong Microsoft Office format compatibility.

  1. 1. Download WPS Office: Go to the official WPS Office website and click Download WPS Office Free.
  2. 2. Install and open WPS Spreadsheet: Install WPS Office, then launch WPS Spreadsheet from the Start menu or desktop shortcut.
  3. 3. Open your Excel workbook: Click Menu > Open, select the .xlsx file, and use the familiar ribbon tabs to review the workbook.
Opens .xlsx, .xlsm, and .csv Excel workbooks so you can inspect cube-related files without extra costFree, lightweight Office suite with a familiar ribbon interface for easy migration from Microsoft ExcelPreserves existing workbook content and PivotTables for everyday review, while advanced Power BI drill-through remains an Excel/Power BI featureCompatible with Microsoft Office file formats for seamless sharing with Excel users
microsoft office alternative - wps office

Frequently Asked Questions

Why does drill-through work in my Excel PivotTable but not from CUBEVALUE cells?

CUBEVALUE and similar cube functions return only the calculated value; they do not retain the PivotTable row, column, or member context that Excel needs to request transaction details from the Power BI or Analysis Services model.

Can WPS Office fix Excel cube-formula drill-through to Power BI?

No. This behavior is part of Microsoft Excel's Power BI data model integration. WPS Office can open and edit the workbook as a compatible Excel alternative, but the actual drill-through action must be done in Excel or Power BI.

What is the fastest way to see detail records behind a CUBEVALUE formula?

Locate the PivotTable connected to the same model, right-click the matching value cell, and choose Show Details or double-click the cell. Excel then creates a new worksheet with the underlying transaction rows.

Will WPS Spreadsheet open an Excel file that contains cube functions and PivotTables?

Yes, WPS Spreadsheet is compatible with Excel .xlsx files and can open workbooks that contain formulas and PivotTables for review and editing, although full live Power BI drill-through requires Microsoft Excel or Power BI.

Can I automate drill-through from a cube formula cell?

You can try a VBA macro or a separate filtered query that reads the cell member keys and requests detail rows, but this is a custom workflow and may require DAX or MDX knowledge.