The Magic of RELATED & RELATEDTABLE functions in DAX


DAX (Data Analysis eXpressions) introduces the functions RELATED and RELATEDTABLE for following & navigating through the chain of relationships in Power BI, Power Pivot & SSAS Tabular and It is used to retrieve related data from another table.

Both of these functions are more powerful than VLOOKUP in Excel in a couple of ways. First, VLOOKUP only returns the first match – there is no promise of referential integrity and no assurance there was not another row that would also have matched the lookup. Second, instead of requiring the lookup table to be organized in a particular way, with certain columns on the left-hand side, DAX functions rely on relationships between the two tables, and those relationships may involve any of the columns in a table.

RELATED(Column) follows existing many-to-one relationship(s) from the many side to the one side and returns the single matching value from the other table. In other words, RELATED can access the one-side from the many-side because there is only one rows exists in the related table and if no matching row exists, RELATED will return BLANK.

Let’s take an example shown in the below Data Model :

data model

Figure 1

In the FactSales table, we can add two calculated columns using these formulas:

  1. =RELATED(Products[ProductName])                                               
  2. =RELATED(Categories[Product[ProductCategoryName])         

4.jpg

Figure 2

These formulas will add two columns to the FactSales table, the first containing the name of each Product and the second containing the Categories name. The first of these examples follows a single relationship from FactSales to Product, while the second formula must navigate two relationships: from FactSales to Product and then from Product to Categories. Authoring this formula requires only that you know the name of the column from which you want to get a value.

5.jpg

Figure 3

If you are on the one-side of the relationship and you want to access the many-side, then RELATED can not be used because many rows from the many side of the relationship might be available for a single row on the one side.

In that case, you can use RELATEDTABLE. It returns a table containing all the rows related to the current one.

For example, if you want to know how many products are in each category, you can create a calculated column in Categories table with this formula:

Total Products = COUNTROWS(RELATEDTABLE(Product))                   

relatedtable1.jpg

Figure 4

RELATEDTABLE(Table) follows a relationship in either direction (many-to-one or one-to-many) and returns a table containing all the rows that are related to the current row from the specified table. This is very useful when you want to find all the transactions associated with a particular row of a related table.

RELATEDTABLE is a function that returns a table and not a scalar value. This means that this function cannot be used by itself to define a calculated column or a measure. Instead this function can only be used to provide an intermediate result that is in turn an argument to another function, such an aggregation function.

Let’s create a calculated column in the Products table with the syntax shown below:

 =SUMX(RELATEDTABLE(FactSales), FactSales[SalesAmount])

This formula says that we want to first construct a table that contains the rows from FactSales that are related to the current row in Products table.

RELATEDTABLE(FactSales) assumes we have a current row and there is a relationship between the current table i.e. Products and the FactSales table. Once we have the table containing the related rows from the sales transactions, we will then take the SalesAmount from each row, and then add those sales amounts.

1

Now, Let’s go ahead and place this formula into a calculated column in the Dates table.

2.jpg

Finally, let’s place the exact same formula with no changes whatsoever into a calculated column in the Customers table.

3

 

While we get completely different numbers in each of those three tables, we will see that we’re getting essentially the same thing: A total of the sales transactions broken down by each product, or for each date, or by each customer.

The construct of aggregating across a related table of transactions is very common and provides a powerful way to break down transactions across the rows of a related table.

There are no of useful applications of both of these functions in Power BI and I hope you will find comfortable using them in your day to day work.

 

Previous How do relationships work in Power BI?
Next Power BI September 2017 Update

No Comment

Leave a Reply