Blog

Related Functions in DAX

The beginner level attendees of our Power BI training often find it difficult to fetch values across the tables in a data model. This problem is solved using the Related functions. The two Related functions discussed in this blog are RELATED() and RELATEDTABLE(). For both of these functions to work properly, a valid relationship must exist between the two tables in a data model.

Using RELATED()

let’s look at some functions which can help you fetch data from other tables. RELATED() takes value from the one side of the one-to-many relationship and populates it to the many side of the relationship. To use RELATED() for bringing the list price value from the Product table into the Sales table:

1. Right click on the Sales table in the Fields
2. Click on New Column.

Column tools open.

3. Type the following formula in the formula bar.

Notice that the intellesence detects that currently List Price does not exist in Sales Table. So intellisense does not give option to select a column from the Product table. We will use RELATED() function to activate the relationship.

4. Type RELATED in the formula bar after =.
5. Select the List Price from the Product table.

Your DAX expression should look similar to the expression below. In this Expression:

• A calculated column with each row containing the respective List Price from the Product table has been created.

6. Click on Data tab in the left Ribbon.
7. Click on Sales table in the Fields.

Notice the current list price column at the end.

Using RELATEDTABLE()

RELATEDTABLE() takes values from the many side of the one-to-many relationship and populates the one side of the relationship. Since there are multiple values for RALTEDTABLE() takes the table name as the input argument. To use the RELATEDTABLE() function for calculating the number of sales in Customer Table:

1. Right click on the Customer table in the Fields
2. Click on New column.

Column tools open.

3. Type the following formula in the formula bar and press Enter.

In this Expression:

• The rows in the Sales table have been counted.

4. Click on Data tab in the left Ribbon.
5. Click on Customer table in the Fields

As you can see, row context does not follow relationship. RELATEDTABLE() allows us to traverse the chain of relationship.

6. Click on the Number of Sales (CC)

Column tool box appears containing the formula bar.

7. Add RELATEDTABLE() before the sales in the formula.

Your DAX expression should look similar to the expression below. In this Expression:

• The related number of rows from the sales table are counted based on the incoming filter context.

Notice the change of values in the Number of Sales (CC) column.                             The Number of Sales (CC) gives the number of Sales transactions by each customer.

RELATED() vs. RELATEDTABLE()

RELATED() and RELATEDTABLE() are the two functions used to traverse one-to-many relationship in DAX. The few key differences between the two functions are:

	RELATED()	RELATEDTABLE()
Input Argument	Column name	Table name
Output	Single value from the current row.	A table of values
Takes values from	one side of relationship	many side of the relationship
Populates	many side of the relationship	one side of relationship

Conclusion

RELATED() and RELATEDTABLE() are the two Related functions used to traverse relationships in a data model in Power BI. RELATED() takes value from one side of the relationship and populates the many side of the relationship. Whereas, RELATEDTABLE() takes values from many side of the relationship and populates the one side of the relationship. This blog used the RELATED() and RELATEDTABLE() functions to solves a business problems and later provides a comparison between the two functions. Are you a data analyst and want to learn more about Power BI? Why not sign up for Power BI training in Australia. We provide the following Power BI trainings: Power BI Essentials Power BI Advanced Power BI DAX