Forum Discussion
Power BI hierarchies missing when data model consumed in Excel
I posted this issue in the Power BI forums.
https://community.powerbi.com/t5/Issues/Power-BI-hierarchies-missing-when-data-model-consumed-in-Excel/idc-p/2352639#M76224
After internal review, the Power BI team said this is an Excel issue and directed me to post here.
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 in Power BI Desktop
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.
8 Replies
- This is an issue in Fabric's Direct Lake mode as well.
Any update Microsoft? I'd like to use PBI dataset instead of SSAS - this will likely keep me from doing so. PBI is good product, make it great by offering flexible options between PBI and Excel without introducing complexity to make it work. Thanks.
- For Analyze in Excel, only Power BI semantic models that use Import mode preserve hierarchies in the PivotTable Fields in the Excel workbook. Power BI semantic models that are built on DirectQuery or composite models don't retain their hierarchies when you use Analyze in Excel.
- For Analyze in Excel, only Power BI semantic models that use Import mode preserve hierarchies in the PivotTable Fields in the Excel workbook. Power BI semantic models that are built on DirectQuery or composite models don't retain their hierarchies when you use Analyze in Excel.
- I opened a support ticket on this topic some time ago. They acknowledged that it does not work but labeled it as "by design" so I do not anticipate a fix anytime soon.
I am going off on a tangent here: with the dataset published in import mode, were you able to actually use the hierarchy in Excel? When I connect to such a dataset, I can see the hierarchy (just like in your screen capture), but when I go ahead and actually try selecting it, an error dialog pops up.
ovidiubaciuIf I open Excel with models that use only import mode I can work with hierarchies otherwise they just don't go DIrect Query/Dual
- same topic... is there any news?
thx ManuelIs this issue solved yet? please let us know ManuelK
