Best way to create a hierarchy

When using hierarchies what is the best way of making them. Lot of topics shows how to drag & drop inside power bi to create hierarchies but I would like to have the hierarchy already in the model.

The picture shows how it has been done now. All of the tables related to this hierarchy table have all of the hierarchy levels flatten out the same way as in the hierarchy table.

the relation is based on combining all of the levels in the Franchise_Hierarchy_Combined for all of the related tables.

What could be a good reason to do this the way it has been done. I hear people say because of the filter that is being used by the user for the hierarchy ??

it just looks stupid to repeat for every related table all of the hierarchies.

I want to make this a real hierarchy and use the hierarchy functions. What could be a pitfall of modifying this. In concern with the filters being used ?

This looks ok as is for the Hierarchy component, but the naming conventations are not good enough. Each column needs to be logically named as to what it is so that anyone can understand what it is.

Maybe this has come directly out of a database. Database usually have horrible naming conventions to them as they don’t seem to care as much about it. In Power BI though it’s important in my opinion.

Really to me it doesn’t even really have to be called a heirarchy, it’s probably just different dimensions within a lookup table. (just normal columns of information that are associated to each other)

A lot of the information you will need to set this all up as a model is contained within this particular course module


1 Like

Thanks Sam,

Yes it is a hierarchy that is true and yes the naming conventions are not good. The report has filters like : hierarchy_1 and Hierarchy_2. Depending on the top level you filter on (franchise). The levels in the hierarchy have different meanings. This is very confusing indeed and difficult to understand.

I am going to advise to change this in a more meaningful way so everybody can easily understand it. Just something that you haven’t gone into… It’s not necessary to repeat all of the levels for the tables that use the hierarchy right?

In my opinion you only need to connect the lowest level to the hierarchy item and all of the above levels will be automatically referred and can be used in rolled up totals en for drilling up-down purposes.

I would like to minimize the redundancy as must as possible which off course would be beneficial for the amount of memory used and will have a impact on the performance…

That is right isn’t Sam?

kind regards,
Sjaak Wooning

If I’m understanding you correctly then yes you are correct that you only need to link the lowest level of the hierarchy.

I just think of this as a simple table with additional fields though. I don’t think about it a hierarchy per se.

Somewhat cover this idea here