RELATED Function Power BI DAX with Example

In Power BI, the RELATED function in DAX (Data Analysis Expressions) is used to fetch a related value from another table. It works within a row context to retrieve a corresponding value from a related table, which is linked through a relationship.

Here’s a basic explanation and example of how the RELATED function works:

Syntax

dax

RELATED(<column>)

• <column>: The column from the related table that you want to retrieve.

How It Works

1. Relationships: RELATED relies on existing relationships between tables in your data model. There must be a relationship between the table you are working in and the table from which you want to retrieve the data.
2. Row Context: RELATED operates in a row context, meaning it evaluates on a row-by-row basis within the table where it is used.

Example Scenario

Imagine you have two tables:

• Sales: Contains sales transaction data.
  • Columns: OrderID, ProductID, Quantity, SalesAmount
• Products: Contains product details.
  • Columns: ProductID, ProductName, Category, Price

You want to add a calculated column in the Sales table to fetch the ProductName from the Products table.

Step-by-Step Example

1. Ensure Relationship: Ensure there is a relationship between the Sales table and the Products table on the ProductID column.
2. Create Calculated Column:
   • Go to the Sales table in Power BI.
   • Create a new calculated column using the following DAX formula:

dax

Product Name = RELATED(Products[ProductName])

3. Result: This new column, Product Name, will now contain the name of the product for each sales transaction, fetched from the Products table.

Practical Use Cases

• Enhancing Data Models: Fetch additional details from related tables to enrich your data model.
• Reporting: Include related data in reports without having to manually join tables.

Tips

• Performance Considerations: Be mindful of performance when using RELATED extensively, especially with large datasets, as it can affect performance.
• Alternative: For complex data models or performance optimization, consider using other DAX functions or data model designs, such as LOOKUPVALUE or combining tables in the query editor.

Common Pitfalls

• Missing Relationships: If there’s no relationship between the tables, RELATED will not work.
• Cardinality Issues: Ensure the relationship is appropriately set (e.g., one-to-many) for RELATED to function correctly.

By understanding and effectively using the RELATED function, you can leverage Power BI’s capabilities to create more insightful and connected data visualizations.