Skip to content
  1. We help small teams achieve big things.
  2. Our Technical Fellow just became a Microsoft MVP!
  3. We run the Azure Weekly newsletter.
  4. We just won 2 awards.
  5. We just published Programming C# 8.0 book.
  6. We run the Power BI Weekly newsletter.
  7. Our NDC London 2020 is now available online!
  8. We are school STEM ambassadors.
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:

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.

Carmel Eve

Software Engineer I

Carmel Eve

Carmel has recently graduated from our apprenticeship scheme.

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. She has been involved in every aspect of the solutions built, from deployment, to data structures, to analysis, querying and UI, as well as non-functional concerns such as security and performance.

Throughout her apprenticeship, she has written many blogs, covering a huge range of topics. She has also given multiple talks focused on serverless architectures. The talks highlighted the benefits of a serverless approach, and delved into how to optimise the solutions in terms of performance and cost.

She is also passionate about diversity and inclusivity in tech. Last year, she became 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.