Skip to content
Carmel Eve By Carmel Eve Software Engineer I
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:

Showing table containing "Child's Name", Parent and DoB.

The has children column in the parent's table is now erroring as it is looking for a column which no longer exists:

Removing "Number of Children" column from the parents table, HasChildren now erroring.

We can then link these tables in the data model:

Showing tables linked in 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):

Number of children = COUNTROWS(Children). Each row shows 14.

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:

Using RELATEDTABLE in calculation. Number of children by parent in correct.

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:

Error when trying to show city in children table.

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:

Using RELATED, correct city shown.

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:

Showing graph of parents by city and table of number of children by parent.

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:

Showing if filter bar chart, filter is applied to table.

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:

Showing filtering table does not filter bar chart.

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.

Power BI Weekly is a collation of the week's top news and articles from the Power BI ecosystem, all presented to you in one, handy newsletter!

Carmel Eve

Software Engineer I

Carmel Eve

Carmel is a software engineer, LinkedIn Learning instructor and STEM ambassador.

Over the past four years she has been focused on delivering cloud-first solutions to a variety of problems. These have ranged from highly-performant serverless architectures, to web applications, to reporting and insight pipelines and data analytics engines.

In her time at endjin, she has written many blog posts covering a huge range of topics, including deconstructing Rx operators and mental well-being and managing remote working.

Carmel's first LinkedIn Learning course on how to prepare for the Az-204 exam - developing solutions for Microsoft Azure - was released in April 2021. Over the last couple of years she has also spoken at NDC, APISpecs and SQLBits. These talks covered a range of topics, from reactive big-data processing to secure Azure architectures.

She is also passionate about diversity and inclusivity in tech. She is a STEM ambassador in her local community and is taking part in a local mentorship scheme. Through this work she hopes to be a part of positive change in the industry.

Carmel won "Apprentice Engineer of the Year" at the Computing Rising Star Awards 2019.

Carmel worked at endjin from 2016 to 2021.