Using Power BI Hierarchies in Excel

[markplace]

My Power BI model contains several Hierarchies.  They work as expected in Power BI.

However when I view my model in Excel they are missing.

Am I doing something wrong?

[parry2k]

@markplace are you using analyze in excel.

[markplace]

yessir

[parry2k]

@markplace I have to check it but maybe hierarchies are not supported in analyze in excel.

[markplace]

I hope that's not the case. We're transitioning from on-prem SSAS tabular to Power BI and told our customer "it's going to be better". Now I have to tell them "well, not so much".

[parry2k]

@markplace not sure what you mean, it is working, see my last reply.

[markplace]

Power BI

Excel (Analyse in Excel)

[v-zhenbw-msft]

Hi @markplace ,

Sorry for that we have a test and there is a Hierarchy in Excel.

We suggest you can uninstall the Analyze in Excel feature and download the latest update Analyze in Excel, then try again.

Also you can refer this Requirements.

If it doesn’t meet your requirement, we suggest you to open a ticket here for help if you are a pro user: https://powerbi.microsoft.com/en-us/support/

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[markplace]

Thanks. I unistalled and reinstalled the latest "analyse in excel". Still not seeing hierarchies.

[v-zhenbw-msft]

Hi @markplace ,

This is really strange. Sorry for that, We cannot reproduce the issue here and also not found an effective solution. 

Now all we can do is suggest you open a Ticket : https://powerbi.microsoft.com/en-us/support/

The best way is leting engineers solve the issue from your perspective.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[markplace · Accepted solution]

In case anyone is interested, we've traced this problem down to the fact that I'm using a composite model.

Hiearchies show up fine in Excel as long as you import.  Mixing import and direct query is a fail when it comes to hierarchies in Excel. 😱

[v-zhenbw-msft]

Hi @markplace ,

We are glad you found the cause of the issue.

We suggest you can mark your reply as an answer, it will help others to find the it more quickly.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[nicg]

I just ran into this issue recently and it cost us a lot of time. Anything we put in our model needs to be able to work in Power BI and Excel. Since we need hierarchies, I guess we just can't ever use direct query for anything.

Is this going to be addressed in the near future?

[parry2k]

@markplace oh wow! Who would have think of that? Good to know. Thanks for sharing your finding, and I'm sure many users will find it helpful, well done.

[markplace]

Actually I found that it is a "benefit" of using Direct Query.  Anything other than import will bork your hierarchies in Excel.

[parry2k]

@markplace I just checked in my model and was able to see the hierarchy in Analyze in Excel, see screen shots below

Hierarchy in ExcelHierarchy created in Power BI Data Model

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡