Daily DAX : Day 201 RELATED

 The RELATED function in Power BI DAX (Data Analysis Expressions) is used to fetch a value from a related table in a data model, based on an existing relationship between tables. It is particularly useful in scenarios where you need to pull data from a "lookup" or related table into a table that is on the "many" side of a one-to-many relationship.

Syntax

dax

RELATED(ColumnName)

    ColumnName: The name of the column from the related table whose value you want to retrieve.

How It Works

    The RELATED function works within the context of a table that is on the "many" side of a relationship.

    It retrieves a value from a column in a related table (typically on the "one" side) for the current row.

    A relationship must already exist between the tables in the data model (e.g., defined in the Power BI model via foreign key-primary key relationships).

Use Case

The RELATED function is commonly used when you need to combine data from related tables for calculations or reporting without explicitly joining tables in a query. For example:

    Retrieving product details (e.g., product name, category) from a product table into a sales table.

    Pulling customer information (e.g., customer name, region) into an orders table.

Example

Suppose you have two tables in your Power BI model:

    Sales (contains sales data with columns: OrderID, ProductID, Quantity, SalesAmount)

    Products (contains product details with columns: ProductID, ProductName, Category)

The tables are related via the ProductID column (a one-to-many relationship from Products to Sales).

You want to add a calculated column in the Sales table to show the ProductName for each sale. You can use the RELATED function as follows:

dax

ProductName = RELATED(Products[ProductName])

    This formula creates a new column in the Sales table.

    For each row in the Sales table, it looks up the corresponding ProductID in the Products table and retrieves the ProductName.

Key Points

    Context: The RELATED function works in row context (e.g., in calculated columns or within measures that iterate over rows).

    Relationship Requirement: A direct relationship must exist between the tables. If no relationship exists, you may need to use functions like LOOKUPVALUE or define a relationship in the model.

    Performance: Using RELATED is efficient as it leverages the existing relationships in the data model, but overusing it in large datasets can impact performance.

    Direction: The function works from the "many" side to the "one" side of the relationship. To go the other way (from "one" to "many"), you might use RELATEDTABLE.

Practical Use Case Scenario

Imagine a retail company analyzing sales data:

    The Sales table records each transaction with ProductID and SalesAmount.

    The Products table contains ProductID, ProductName, and UnitPrice.

    You want to create a report that shows the product name alongside each sale without duplicating data in the Sales table.

By adding a calculated column using RELATED in the Sales table:

dax

ProductName = RELATED(Products[ProductName])

You can now use ProductName in visuals or calculations, such as grouping sales by product name or category.

Limitations

    RELATED cannot be used if there is no direct relationship or if the relationship is ambiguous (e.g., multiple paths exist).

    It does not work in measures directly unless combined with an iterator (e.g., SUMX, AVERAGEX) that establishes row context.

    It retrieves only one value per row; for multiple related rows, use RELATEDTABLE instead.

Alternative Functions

    RELATEDTABLE: Retrieves a table of related rows (used when you need multiple rows from the "many" side).

    LOOKUPVALUE: Can be used when no relationship exists, but you need to match values based on a condition.

    USERELATIONSHIP: Used to activate an inactive relationship for a specific calculation.

Conclusion

The RELATED function is a powerful tool for simplifying data retrieval across related tables in Power BI, making it easier to enrich your data model with contextual information. It is best used in calculated columns or measures with row context, and it relies on well-defined relationships in the data model. By using RELATED, you can streamline reporting and analysis without complex joins or redundant data.