Power BI DAX’s RELATED vs RELATEDTABLE Function

Today, let’s talk about the RELATED and RELATEDTABLE functions. These functions are used to access data from related tables in your data model. If you have worked with any databases or BI tools, you must have created relationships between tables to form a snowflake schema or star schema. Power BI is really efficient when you create a data model by following star schema.

Now, let’s understand what RELATED and RELATEDTABLE functions are and how can you utilize them in your next Power BI project. You can use the RELATED function to retrieve a single value from a related table based on the current row context. If you are familiar with LOOKUP in Excel, RELATED works exactly the same. For example, if you are looking to get a salesperson’s information such as a name from the Employee table to the Sales table to see their sales amount side by side. it will return a blank value if there is no matching row in the related table or if there are multiple matching rows.

The syntax of the RELATED function is:

RELATED(<column>)

where <column> is the name of a column you want to use from a related table.

Meanwhile, The RELATEDTABLE function allows you to retrieve a whole table from a related table based on the current filter context. For example, if you have a table of customers and a table of sales transactions, you can use the RELATEDTABLE function to get all the transactions for each customer.

The syntax of the RELATEDTABLE function is:

RELATEDTABLE(<table>)

where <table> is the name of the related table from which you want to get the related fields. The RELATEDTABLE function will return an empty table if there is no matching row in the related table.

Let me take an example where we would like to calculate the discount given to a female customer. Our company provides 10% discounts to female customers and the customer’s gender information is stored in the DimCustomer table. To calculate a discount measure, we will use the following DAX formula:

RELATED DISCOUNT =
SUMX (
    FactInternetSales,
    FactInternetSales[SalesAmount]
        * IF ( RELATED ( DimCustomer[Gender] ) = "F", 0.1 )
)

We are using an iterator function SUMX here, where the first argument is the FactInternetSales table that has SalesAmount and we would like to check whether the customer’s gender is “F”, if so then we want to apply a 10% discount on it. Here we have used RELATED to get the customer’s gender field as an iterator function will not show fields from any table other than FactInternetSales.

REMEMBER! The RELATED function requires a relationship between tables, and it only works if the relationship is one-to-many or one-to-one. This means that for each row in the current table, there is at most one matching row in the related table.

Now, let’s say you need to create a calculated column instead of a measure in the FactInternetSales table. We will use the following DAX formula using RELATEDTABLE:

RELATEDTABLE DISCOUNT =
MAXX (
    RELATEDTABLE ( DimCustomer ),
    FactInternetSales[SalesAmount]
        * IF ( DimCustomer[Gender] = "F", 0.1 )
)

In the above formula, we are using an iterator function as well. However, here we use MAXX as I wanted to explain the row context here. We have a one-to-many relationship between DimCustomer and FactInternetSales table. Whenever we use RELATED or RELATEDTABLE, if you are bringing the value from one side, it will always return a single item. In our case, regardless of which iterator function we use, such as SUMX, AVERAGEX, MAXX, or MINX, it will return 10% for CustomerKey entries where the customer’s gender is female.

Since the formula above will create a column and if we need to use it in a visual, we can aggregate it by simply using the SUM function to show the total discount.

RELATED or RELATEDTABLE functions have some limitations and best practices that you should know about, such as not working if there is no relationship between the tables or if the relationship is many-to-many. Using them excessively or on large tables can affect your model’s performance. You can replace them by using proper star schema design and using CALCULATE or CALCULATETABLE functions with filter arguments.

RELATED and RELATEDTABLE functions are very handy when it comes to creating calculated columns and measures that require fields from related tables.

In conclusion, the RELATED and RELATEDTABLE functions are handy tools for accessing data from related tables in Power BI. Although these DAX functions can help you create more complex calculations and enrich your data model with additional information, they should be used with caution and understanding of their behavior and impact.

If you’re looking to level up your DAX skills, check out my blog post on other relationship functions! USERELATIONSHIP and CROSSFILTER

If you enjoyed this blog post and learned something useful and would like to receive notifications about my new blogs, please follow and subscribe to my Medium account. Also, please don’t forget to give me a clap and save it on your reading list. Stay tuned for my next blog posts with tons of other Power BI tips and tricks. Feel free to leave your comments and feedback below. Thank you for visiting!