RELATED and RELATEDTABLE in DAX | Power BI Tutorial

Power BI is a powerful tool that allows you to build dynamic reports by connecting tables through relationships. In this tutorial by Justin Vogel from Pragmatic Works, we dive into two essential DAX functions: RELATED and RELATEDTABLE, which can greatly enhance your ability to work across multiple tables in your Power BI data model. These functions make it easier to pull data from related tables and create smarter, more dynamic reports. Let’s break down these functions and how they can be applied effectively in Power BI.

Understanding Relationships in Power BI

Before using the RELATED and RELATEDTABLE functions, it’s crucial to understand how relationships work in Power BI. Relationships typically follow a one-to-many or many-to-one model, where one table (the “one” side) is linked to another (the “many” side) via a shared column. For example, in a customer-orders relationship, one customer can have many orders. Understanding the direction of these relationships is key to choosing the right DAX function.

The RELATED Function

The RELATED function allows you to pull a value from a related table into your current table, based on a pre-established relationship. This function works from the "many" side of the relationship to the "one" side. Think of it like Excel’s VLOOKUP but much more powerful and dynamic, as it works with the entire relationship model and adapts to any filters applied.

• Use Case: If you want to bring the customer's name from the "Customers" table into the "Orders" table, you can use the RELATED function.
• How it works: RELATED uses the relationship between the customer ID in the "Orders" table and the customer ID in the "Customers" table to pull the corresponding customer name.
• Formula Example: RELATED(Customers[CustomerName])

The RELATEDTABLE Function

Unlike the RELATED function, RELATEDTABLE works in the opposite direction. It is used to bring in a table of related rows from the "one" side to the "many" side. This is especially useful for aggregations, where you want to count or sum the rows related to a specific item (e.g., counting the number of orders for each customer).

• Use Case: If you want to count how many orders each customer has, you would use the RELATEDTABLE function.
• How it works: RELATEDTABLE returns a table of all orders related to the current customer, which can then be counted using DAX functions like COUNTROWS.
• Formula Example: COUNTROWS(RELATEDTABLE(Orders))

Applying RELATED and RELATEDTABLE with Aggregation Functions

One of the best ways to use RELATEDTABLE is in combination with aggregation functions like SUMX or AVERAGEX. These iterator functions evaluate an expression row by row over a table and then summarize the results.

• SUMX: This function allows you to sum values that are evaluated row by row, such as multiplying the quantity and price of an order to calculate total sales for each customer.
• AVERAGEX: Similar to SUMX, but calculates the average of an expression over a table. For example, you can calculate the average amount spent per order by each customer.

Step-by-Step Example: Total Sales Calculation

Let’s see how to use the RELATEDTABLE and SUMX functions to calculate total sales per customer. In this case, we will multiply the order amount by the quantity ordered, and then sum the results for each customer.

1. Create a new column in the "Customers" table.
2. Use the following formula to calculate total sales: SUMX(RELATEDTABLE(Orders), Orders[Amount] * Orders[Quantity])
3. The result will show the total sales for each customer, taking into account both the amount and the quantity ordered.

Conclusion

In this tutorial, we covered the RELATED and RELATEDTABLE functions in DAX, which are essential for working with data across multiple tables in Power BI. RELATED helps you pull values from a related table to the current row, while RELATEDTABLE allows you to bring in related rows for aggregation purposes. By understanding these functions and applying them in your reports, you can create more powerful and dynamic Power BI dashboards. For more advanced Power BI tips and tricks, stay tuned for more tutorials from Pragmatic Works.

Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on Power BI  and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.