The color is specified in the Product table, which is on the one side of the one to many relationship with the Sales table. Imagine we want to add a column in our Sales table that indicates the color of each sale made. The row context iterates through the table on the many side, and finds the one corresponding row on the table on the one side. The RELATED function is used to force the row context to propagate from the many side to the one side. In order to propagate the row context from table to table, we need to use the RELATED and RELATEDTABLE functions. The row context is not propagated through relationships, hence why Power BI doesn’t allow the creation of calculated columns computed from two or more different tables. Understanding how the row context affects relationships is easy, because it doesn’t. Now that we have seen how tables can be related to one another, let’s look at how these relationships affect our evaluation context.
The direction of the relationship is shown by an arrow pointing to either or both tables. When we look at the data model in Power BI, the one side is denoted by a “1”, and the many side is denoted by an “*” sign. It is possible to have many to many relationships, but these make the data model more complex. Hence in this relationship, the Product table is on the one side, and the Sales table on the many side. One same product can be sold many times, so it can feature more than once in the Sales table. The sides can be either the “one” side or the “many” side. It is standard practice to keep relationships unidirectional wherever possible, since bidirectional relationships are harder to predict. The relationships can be either unidirectional or bidirectional. The direction of the relationship dictates how Power BI will apply filters from one table to another, meaning which table is allowed to filter the other. Relationships have a direction and two sides. The Sales and Product tables are clearly related to each other, since the Sales table tells us which products where sold, and the Product table gives information about the products themselves, like their colour, brand, and price. We have a “Sales” table containing the details of each sale made and a “Product” table, containing details about the products available in the store, amongst others. Say we have a data model containing data on the sales of certain products from a store. Let’s reuse the example from our previous post. In this post, we will see the different types of relationships and how these affect the output of our DAX code. These data tables are connected with each other through relationships. Most data models, like the ones we use to visualise data in Power BI reports, are composed of many data tables. There is one further key aspect to take into account when understanding how our results are calculated – relationships. In our previous article on Evaluation Contexts in DAX, we learned about the two types of evaluation contexts, namely the filter and row contexts – and how these dictate how any formulas that we write in DAX are calculated. TLDR After learning about the two different types of evaluation contexts in our previous post, we now talk about table relationships and how these interact with the filter and row contexts to condition the output of our DAX code. By Elisenda Gascon Apprentice Engineer II 17th January 2022