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.
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?