AdministratorHello,
I utilize Analyze in Excel quite extensively to create connected Excel tables to semantic models (I am not referring connected pivot tables) . I noticed that the behaviour of these connected tables has changed since the latest update. It seems that when a connected table is generated using a MDX query (the default when drilling through a pivot table), "amount" columns seem now to be excluded by default. If I rewrite the query using DAX syntax, these columns show up again. This behaviour also only seems to happen when the model is published to PowerBI.com. For example, the following MDX query will not return any columns that are flagged in the service as an "Aggregation" type:
DRILLTHROUGH MAXROWS 500000 SELECT FROM [Model]
WHERE (
([Measures].[BurdenedCost_USD],
[ProjectInquiry_Test].[PROJECT NUMBER].&[036C243029])
)However, if I rewrite the query using DAX, the "aggregation" type columns are returned as expected:
EVALUATE
CALCULATETABLE(
DETAILROWS( [BurdenedCost_USD] ),
ProjectInquiry_Test[PROJECT NUMBER] = "035C230306"
)
The table I am referencing in the above queries does not have an explicit "detail rows expression" specified, so it will just return whatever is in the table that the measure is housed in. I have tested that an explicit detail rows expression will force MDX to return "amount" / "aggregation" type columns when drilling through on a pivot table in Excel. So the moral of the story is to always be explicit with what you want to return when drilling through.
All that being said, I would like to understand whether this change in functionality / behaviour was intended, or perhaps a bug. murray-kp DataZoe any thoughts on this? This could significantly impact users exploring data in Analyze in Excel who aren’t familiar with detail rows expressions in tabular models.