Daily DAX : Day 174 RELATEDTABLE

 The RELATEDTABLE function in Power BI's Data Analysis Expressions (DAX) is used to retrieve a table that is related to the current table through a relationship in the data model. It’s particularly useful in scenarios where you need to perform calculations or aggregations based on data from a related table, typically in a one-to-many or many-to-one relationship.

Syntax

RELATEDTABLE(<tableName>)

    <tableName>: The name of the related table from which you want to retrieve rows.

How It Works

    RELATEDTABLE follows the relationships defined in the data model and returns a table containing all the rows from the specified related table that correspond to the current row in the context of the calculation.

    It operates within a row context, meaning it’s typically used in calculated columns or measures where a specific row is being evaluated.

    The function is often paired with aggregation functions like SUM, COUNT, AVERAGE, etc., to summarize data from the related table.

Key Points

    Relationships: The function relies on an active relationship between tables in the data model. The relationship must be properly defined (e.g., a foreign key in one table linking to a primary key in another).

    Direction: It works in the direction of the relationship. For example, if you’re in a "one" side table (like a Customers table), RELATEDTABLE can fetch rows from the "many" side (like an Orders table).

    Filter Context: The returned table respects any filters applied in the current context, including those from slicers, filters, or row context.

Use Case

Let’s say you have two tables in your Power BI model:

    Customers: Contains customer details (CustomerID, Name, etc.).

    Orders: Contains order details (OrderID, CustomerID, OrderAmount, etc.).

These tables are related via the CustomerID field, where one customer can have multiple orders (a one-to-many relationship).

Scenario: Calculate Total Orders per Customer

You want to create a calculated column in the Customers table to show the total order amount for each customer.

DAX Formula

dax

TotalOrderAmount = 

SUMX(

    RELATEDTABLE(Orders),

    Orders[OrderAmount]

)

    RELATEDTABLE(Orders) retrieves all rows from the Orders table that match the CustomerID of the current row in the Customers table.

    SUMX iterates over those rows and sums the OrderAmount values.

Result

For each customer, the calculated column displays the total order amount based on their related orders.

Another Use Case: Counting Related Rows

If you want to count how many orders each customer has placed, you could use:

dax

OrderCount = 

COUNTROWS(

    RELATEDTABLE(Orders)

)

    RELATEDTABLE(Orders) fetches the related rows from the Orders table.

    COUNTROWS counts the number of rows returned, giving the total number of orders per customer.

When to Use RELATEDTABLE

    Use it when you’re working on the "one" side of a relationship and need to access or aggregate data from the "many" side.

    It’s ideal for scenarios like summarizing sales, counting transactions, or performing row-level calculations based on related data.

Difference from RELATED

    RELATED fetches a single value from a related table (works from "many" to "one").

    RELATEDTABLE fetches an entire table of related rows (works from "one" to "many").

Practical Example

Imagine a retail dataset:

    Stores table: StoreID, StoreName

    Sales table: SaleID, StoreID, SaleAmount

To calculate the total sales per store in a calculated column in the Stores table:

dax

TotalSales = 

SUMX(

    RELATEDTABLE(Sales),

    Sales[SaleAmount]

)

This sums all SaleAmount values from the Sales table for each StoreID.

In summary, RELATEDTABLE is a powerful function for navigating relationships in Power BI and performing calculations based on related data, making it essential for complex reporting and analysis tasks.