Hierarchy not available

[Anonymous]

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

[ibarrau]

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]

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]

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,

[Anonymous]

Hi, 

Thank ibarrau, it works now. But I am facing another issue now 😞

Let me explain. As I said, I have 2 levels of injuries in my report. I have created 2 separated table to do so, one for global level, let's say level 1, one for details, level2.

The problem is the (sum of ) number of injuries is not the same between level 1 and level2.

Here is my data to explain :

So, as you can see, the total number of injuries at level 1 is 36. And at level 2, it's 39. Because one accident can have several injuries, but we only count one for the global, the worst let's say.

My problem is the % calculated in my drill-down for level 1 : it is calculated based on level 2 total, not on level 1 total...

Here are the fields I put in my pie for the drill down : 

Don't know if my model should be built differently ?

Do you have an idea how to solve this ?

Thanks a lot for your help, AnSo

[az38]

Hi @Anonymous 

have a look here https://community.powerbi.com/t5/Desktop/Date-Hierarchy-in-Direct-Query/td-p/657982