Power BI — Navigation through relationships using DAX

I want this to be very short and effective.

We are all quite familiar with the Excel function called Lookups; nevertheless, to refresh your memory, watch the video below.

When designing a calculated column in Power BI, we can utilize the reference columns from that table to develop the logic. However, there are instances when we need to reference columns from other tables, which is only possible if those tables are related to one another.

We can use RELATED and RELATEDTABLE DAX functions to achieve the desired goal.

RELATED FUNCTION

Assume we want to calculate the Cost depending upon the Manufacturer Name in the sales table with the help of Product table as the name resides in it, where the relationship is from Product to Sales as One to Many. Here we will use Related as we want to access the 1-side of the relationship from the Many-side. It can travel to many chain to relationships.

Adjusted_Cost_By_Manuf = IF (RELATED ( ‘Product’[Manufacturer] ) = “Tailspin Toys”, Sales[Unit Cost] * 0.5, Sales[Unit Cost])

RELATEDTABLE FUNCTION

As we saw above, there are ways to move from one to the many sides of a relationship. In the event that we need to move from the many to one side of a relationship, we will utilize relatedtable since it will produce a table with many rows in reference to a single row. The majority of the time, iterators like countx, sumx, etc , use relatedtable function

Product Category [NumOfProducts] = COUNTROWS ( RELATEDTABLE ( Product ) )

At the end of the table, a calculated column that is not dynamic will be formed; nevertheless, we can construct a measure to make it dynamic.

Reference — The Definite Guide to DAX