Forum

How does the RELATE...
 
Share:
Notifications
Clear all

How does the RELATED function work in DAX, and when should I use it to retrieve data from related tables in Power BI?


Posts: 69
Guest
Topic starter
(@Vinay Kumar)
Trusted Member
Joined: 5 years ago

The RELATED function in DAX allows you to access columns from related tables and bring that data into the current table. This function is very helpful when working with tables connected by relationships, allowing you to enrich data in one table with information from another.

Syntax:

dax
 
RELATED(<ColumnName>)
  • ColumnName: The specific column from the related table you want to retrieve. The tables must be connected by a relationship for RELATED to work.

Example 1: Retrieve Product Category for Each Sale

Suppose you have two tables: a Sales table and a Products table. The Sales table includes a ProductID column, and the Products table has columns ProductID and Category. If you want to pull the Category from the Products table into the Sales table, you’d use RELATED like this:

dax
 
Product_Category = RELATED(Products[Category])

This formula will pull in the category from the Products table for each row in the Sales table based on the ProductID relationship. Now, you can easily categorize each sale by product category, which is useful for reporting by category.

Example 2: Calculating Profit Using Related Data

Let’s say you want to calculate the profit for each sale by using data from a related Costs table that includes ProductID and CostPerUnit. By using RELATED, you can pull the cost information into the Sales table and calculate profit:

dax
 
Profit = Sales[SalesAmount] - (RELATED(Costs[CostPerUnit]) * Sales[Quantity])

This formula retrieves CostPerUnit from the Costs table and calculates profit by subtracting the total cost from SalesAmount. Using RELATED in this way makes it easy to create calculations that depend on information stored in different tables.

Why and When to Use RELATED?

  • Data Enrichment: RELATED is ideal for pulling related data into one table to create meaningful calculations and insights.
  • Multi-Table Calculations: When you need to use information from multiple tables, RELATED simplifies the process of accessing that data without creating unnecessary duplications.
  • Relationship-Based Lookups: RELATED performs lookups based on relationships, so there’s no need to create complex joins or custom tables.

Pro Tip: For RELATED to work, ensure that a relationship exists between the tables. Use it in calculations that benefit from the relational model in Power BI, especially when you want to enrich tables with related data rather than merging tables.

Leave a reply

Author Name

Author Email

Title *

Preview 0 Revisions Saved
Share: