Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
PURVI_SANCHETI
Regular Visitor

Hierarchy relationship in Power BI

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.

 

PURVI_SANCHETI_0-1716921462009.png

 

1 ACCEPTED SOLUTION
kpost
Super User
Super User

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///

View solution in original post

1 REPLY 1
kpost
Super User
Super User

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///

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors