cancel
Showing results for 
Search instead for 
Did you mean: 
0

Power BI hierarchies missing when data model consumed in Excel

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

nicg_0-1645133654214.png

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

nicg_1-1645133704056.png

 

Model with single table in DirectQuery mode containing a hierarchy

nicg_2-1645133762752.png

Hierarchy missing in Excel 😞

nicg_3-1645133792029.png

 

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?

Status: Delivered

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

Comments
v-cazheng-msft
Community Support
Status changed to: Investigating

Investigating on it.

 

Best Regards,

Community Support Team _ Caiyun

v-cazheng-msft
Community Support
Status changed to: Accepted

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
Advocate II

Thanks for the update

v-cazheng-msft
Community Support
Status changed to: Delivered

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