Power BI hierarchies missing when data model consumed in Excel

[nicg]

If a Power BI model contains a DirectQuery or Dual mode table, and that same model also contains attribute hierarchies, those hierarchies are not accessible when the model is consumed through Excel.

Example:

Model with single table in import mode containing a hierarchy

Published model viewed from Excel. Hierarchy works fine in import mode! 😁

Model with single table in DirectQuery mode containing a hierarchy

Hierarchy missing in Excel 😞

As we have many Excel users on our Power BI enterprise model and also rely on several hierarchies, this prevents us from optimizing our model with any type of aggregation or hybrid table. We are stuck with import on every table and cannot pursue any advanced refresh or query optimization strategies. 😖

It's frustrating to have so much great work by the Power BI team regarding user defined aggregations, automatic aggregations, hybrid tables, etc. being made useless by a visibility issue in Excel!

Is this a known issue? Is there any workaround? Maybe I'm doing something wrong?

[v-cazheng-msft]

Investigating on it.

Best Regards,

Community Support Team _ Caiyun

[v-cazheng-msft]

Hi @nicg ,

Issue has been reported internally(ICM 290627369). Will follow up this ICM and confirm with product team whether it’s by design. Once there is any update, I will post here.

Best Regards,

Community Support Team _ Caiyun

[nicg]

Thanks for the update

[v-cazheng-msft]

Hi @nicg ，

Thanks for the wait. According to the reply from product team, this is an issue with Excel client, not with Power BI. Please consider asking for help from Excel team:

https://techcommunity.microsoft.com/t5/microsoft-excel/ct-p/Excel_Cat

https://techcommunity.microsoft.com/t5/excel/bd-p/ExcelGeneral

Thanks for your understanding!

Best Regards,

Community Support Team _ Caiyun