Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hierarchy not available

Hi, 

 

I am quite beginner in Power BI and I am stuck on a hierarchy problem.

I have one table in my cube with lesions (for example : head, legs, chest, arms) and one table with lesion details which is a sub-level of the lesions (for head category : i will have eyes, forehead, nose, ..., for legs category, i have knees, upper leg, ...). So more a less a category with a sub category.

I have built a primary key in each table to be able to link with a 1-n relation, so one record in lesion table can have multiple records in lesion_detail table.

So, then I try to create a hierarchy in my power bi report to get a drill down in a pie for example for level one (lesion) to level 2 (lesion_detail). My problem is that when I right click on the category in the lesion table, the "New hierarchy" choice is not available.

Do you know why i don't have the hierarchy option ? is my cube not built in a proper manner to have category hierarchy ?

I would be very grateful if you could help me on this issue...

Thanks a lot, AnneSo

Need Help

5 Replies

  • ibarrau's avatar
    ibarrau
    Super User
    6 years ago

    Hi Anne. Let's start asking, which is your data source? I mean, what is power bi desktop connected to? if you are connected to a tabular model (an analysis services or power bi service) then you should build the hierarchy in the model and not in this connection.

    The good news is that you don't need a hierarchy to make a drilldown. You can just drag the fields on legend (leave details in blank) in the pie chart one under the other and Power Bi will make it drilldown.

     

    Regards,

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi ibarrau, 

       

      Indeed, my data source is a tabular model.

      i can't put my fields one under the other because I have the field "value" in my lesion table and another field "value" in my lesion_details table. So if I put lesion.level 1 field and lesion_detail.level 2 field in the legend of my pie, there is no data displayed at all, it's not compatible.

      Do I have to put all my lesions in the same table with level 1 and level 2 in the table directly ? and then build the hierarchy with level 1 and level 2 ?

      Or do I have to build a hierarchy in my cube ? if so, how can I do this ? 

      Thanks a lot, AnneSo

      • ibarrau's avatar
        ibarrau
        Super User
        6 years ago

        I'm sorry I have just checked again and you shouldn't put anything in Details. Just add level 1 and level 2 in legend. Then drag the value and you will have your drilldown in the pie chart!

        About tabular you can't modify anyting of the model when you have a live connections. It will only let you create measures. If you want anything else you need to modify the source data model.

        Regards,