Forum Discussion

markplace's avatar
markplace
Helper I
6 years ago
Solved

Using Power BI Hierarchies in Excel

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?
Need Help
  • markplace's avatar
    markplace
    6 years ago

    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's avatar
v-zhenbw-msft
Community Support
6 years ago

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's avatar
markplace
Helper I
6 years ago

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. ðŸ˜±

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    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's avatar
    markplace
    Helper I
    6 years ago

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

  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    Community Support
    6 years ago

    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's avatar
    nicg
    Advocate II
    5 years ago

    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?