drill through missing date column, but shows hidden calculated columns

[ibax]

The drillthru (show detail) functionality in an Excel Pivot Table connected to a PowerBI Dataset works fine.

Compared to Excel PowerPivot, the detail records returned to the client doesn't return all visible table columns.

Issue: Non-hidden (=visible) columns of the data type "date" are not returned at all.

BTW: On the other hand, it seems that calculated columns are always visible - even if they have been set to "hidden in reports".

[v-haibl-msft]

@ibax

I'm not very clear about your issue, could you please provide a simple sample for it?

Best Regards,
Herbert

[ibax]

Sample

Screenshot 1:
In the PBIX model I have a table with a visible date column (1st column)
and two hidden calculated column (last two columns)

Screenshot 2:
I'm using Excel to connect to the deployed dataset in Power BI

Screenshot 3:

Drillthrough result set in Excel

• Issue 1: the date column is not returned in the drillthrough result set (Excel), even the column is visible
• Issue 2: the calc columns are returned in the drillghrough result set (Excel), even they are hidden

All other columns behave as expected and only visible columns are returned in the drillthrough result set

If desired I can send you the sample pbix and
Excel file by email to your personal address.

With best regards

Michael

PBIX Table DefinitionExcel Pivot TableDrillthrough Result Table

[v-haibl-msft]

@ibax

You can send to v-haibl@microsoft.com.

Best Regards,
Herbert

[v-haibl-msft]

@ibax

It seems that there is no issue when using connect to data in Power BI ribbon (the result is Excel Pivot Table).

How do you get the data in Sheet2 (the result in Drillthrough Result Table)?

Best Regards,
Herbert

[ibax]

Actually Drillthrough means to get the row details for a selected cell in an existing Pivot Table (linked to a PowerBI Dataset).

Double-Click on Measure-Cell or with context-menu (right-click): "Show Details" executes
a Drillthrough against the Dataset with following MDX-Statement

"DRILLTHROUGH MAXROWS 1000
SELECT FROM [Model]
WHERE ([Measures].[Count],[Datum].[Month].&[Februar])"

and shows the returned records on a new sheet.

The returned records do NOT contain the existing and visible date column (issue 1),
but show the content of the hidden calculated columns (issue 2).

Note: The two issues are independent of each other.

The issue 2 occurs only in case you have more than one hidden calculated column.

Regards

Michael

PS: If desired, we may setup a remote desktop session with Skype

[v-haibl-msft]

@ibax

I can repro the same issue as you.  I’ve reported it internally to Power BI Team: CRI 50906880
I’ll post here once I get any update about it.

Best Regards,
Herbert

[v-qiuyu-msft]

Hi @ibax,

I got below information from PG team: 

Regarding hidden columns, Analyze in XL is a feature that allows Excel to connect to a *dataset* in PowerBI, hiding columns in the *report* doesn't affect the dataset itself.

Best Regards,
Qiuyun Yu

[v-qiuyu-msft]

Hi @ibax,

Regarding the issue "the unhidden column [Date] is not visible in the “Details” table", currently this is by design. "Show Details" (MDX Drillthrough) in Excel is not supposed to return foreign key columns for tabular models, and enabling auto date/time creates hidden date tables for the date columns, and makes these date columns the FK columns for new relationships to the date tables.

Best Regards,
Qiuyun Yu