Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
SHHargrove
Advocate II
Advocate II

Hierarchies in Analyze in excel with Direct Query

Creating my first Direct Query Power BI report and testing Analyze in Excel.  I notice that none of my Hierarchies appear in Excel, even though they do when in Cached mode.  Have I done something wrong, or is that expected behavior?  I would like it to work.

Otherwise, I'm optimistic about this approach.

6 REPLIES 6
Foton
Frequent Visitor

Any updates?

v-shex-msft
Community Support
Community Support

Hi @SHHargrove,

 

>> I notice that none of my Hierarchies appear in Excel, even though they do when in Cached mode. 

Since I'm not very clear your issue, can you share some detail contents?

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

 

In the images below, you'll see that the Product dimension in Power BI includes a "Products" hierarchy.  However in Excel (using Analyze in excel from the same Power BI dataset) the "Products" hierarchy does not appear.

 

Product Dimension in ExcelProduct Dimension in ExcelProduct Dimension in Power BiProduct Dimension in Power Bi

Hi @SHHargrove,

 

If you try to use the hierarchy which created at database, does it analysis in excel succeed? I think excel can't analysis the hierarchy columns which created in power bi.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

 

ExcelExcelThis picture is from Analyze in Excel when not using Direct Query in Power BI.

Clearly the difference is Direct Query.

 

 Was this ever resolved still seems to be the case. They only way to get Hierarchys to show up is to convert the entire model to Import mode. Even if all tables involved in the Hierarchy are moved to Import a single separate table in DQ mode kills the hierarchy from Excel Pivot fields. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors