Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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.
Solved! Go to 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///
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///
User | Count |
---|---|
84 | |
79 | |
71 | |
47 | |
42 |
User | Count |
---|---|
109 | |
54 | |
50 | |
40 | |
40 |