Hierarchy relationship in Power BI

[PURVI_SANCHETI]

Can we create a one to many or one to one relationship in Power BI with a heirachy as a column to connect with another table? I am trying to connect Special Activity heirarchy from one table to Special Activity column of another table. It is showing no reponse. Is there a way i can achieve it? Thank you for the help in advance.

[kpost · Accepted solution]

A Hierarchy is not a column, rather it is a set of columns.  It cannot be used to create a relationship between two tables.

I would recommend using the lowest level in the hierarchy that both tables share as the basis for an active relationship.   For example, you may have a Date hierarchy in your date dimension table that contains Date, Month, and Year.  You would want to use the lowest level, "Date", as the key to create a relationship with your fact table, because it can be unique and be effective in a given many to one relationship.

If the lowest level in the hierarchy does not uniquely exist among different categories "above it" in the hierarchy, then you can create a unique key for that hierarchy level by combining them, and use that as the key.

For example, let's say you have the following values in a hierarchy comprised of three columns, Country, State, City.

US, Illinois, Springfield

US, Oregon, Springfield

In this case, you would probably not want to simply use the "city" in the active relationship.  You would instead want to create a new column combining the City and State in both your dimension and fact table, and use that as the key.

It really depends on your situation.  The goal is to create or use a key that can uniquely identify the lowest shared level in the hierarchy.

Hopefully this has been helpful.

///Mediocre Power BI Advice, but it's free///