Learning DAX and Power BI – Related Tables and Relationships
This is the sixth blog in a series about DAX and Power BI, the first of which can be found here. We are now going to talk about related tables and relationships.
The model we have been working with so far consisted of just one table – a list of people, where they live, their date of birth and the number of children they have. If we now adjust our data model, removing the number of children from the parent table, and adding a second table just for them:
The has children column in the parent's table is now erroring as it is looking for a column which no longer exists:
We can then link these tables in the data model:
We can see that Power BI has correctly ascertained that the link between these two tables is the "Parent" column, and that one parent in the "Parents" table can have many "Children", therefore the link is 1 to many.
These tables now have a relationship.
Relationships and evaluation contexts
Row contexts
Row contexts do not propagate relationships. To illustrate this, if we reference a the "children" table from inside the "parents" table in order to count the number of children (this is following a 1 to many relationship as one parent can correspond to multiple children in the children table):
We can see that the row context applied by each row in the parents' table has not been taken into account when counting the rows in the related table. If we want this context to be propagated we have to be explicit:
By using the RELATEDTABLE function – which was mentioned in the previous blog as one of the table functions, we can run the query over the related table whilst preserving the row context.
This is the same when travelling in the many-to-1 direction. Without specifying that the row context should be preserved when we are accessing a column in the parents table from the children:
We get an error because, as the relationship isn't preserved, 'Parents'[City] corresponds to the entire column. However, by using the RELATED function, which explicitly propagates the context:
In summary, row contexts do not automatically propagate relationships, however they can be explicitly preserved using the RELATEDTABLE (1 to many) and RELATED (many to 1) functions.
Filter contexts
Filter contexts are a little more complicated in that they propagate in the 1 to many direction but not in the many to 1. We can see this if we add two graphs to the report:
On the left we have a bar graph created from the parents' table, which shows the distribution of the parents between the different cities. On the right we have a table created from the children table which shows the number of children for each parent.
If we filter the parents' chart to just include those in London:
We can see that the childrens' table has been filtered down. This is because the filter context which limits the dataset to those in London has propagated down the parents-to-childrens' 1 to many relationship. However, if we go the other way and filter the children table down to just on parent:
Then the parents' graph remains unaffected because the filter context does not propagate down the many children to 1 parent direction of the relationship.
Bi-directional relationships
There is the option of using a bi-directional relationship between tables. This means that the filtering will propagate in both the 1-to-many and many-to-1 directions. This can be very powerful in the case of complex modelling but should only be used with a complete understanding of the models and interactions. Without this it can easily lead to surprising and incorrect results. It is best, as a default, to disable bi-directional propagation and only enable it if absolutely necessary.
Overall
In summary we can build up a model using relationships between tables. These relationships are usually 1-to-many or many-to-1. Row context does not propagate in either direction unless you specifically enable it using the RELATED or RELATEDTABLE functions. By default filter contexts should only propagate in the 1-to-many direction. There is the option to enable bi-directional propagation but this should be done with great care.
