The RELATED function in Power BI is used to fetch a column value from a related table based on an existing relationship. It is commonly used in calculated columns or row-by-row calculations like SUMX.
Syntax: RELATED(<ColumnName>)
- Returns a related value from another table.
- Works only when a relationship exists.
- Used mainly in calculated columns or inside iterator functions.
Key Requirements for RELATED Function
- Valid and Active Relationship Must Exist: RELATED() works only when there is a defined and active relationship between the tables in the data model.
- Works Only from Many Side to One Side: The function retrieves values from the one-side table while being used in the many-side table.
- Requires Row Context: RELATED() must be used in calculated columns or inside iterator functions like SUMX where row-by-row evaluation exists.
- Not Used as a Standalone Measure: It does not work alone in simple measures because measures operate in filter context, not row context.
- Depends on Proper Data Model Design: A clean star schema with correct cardinality ensures RELATED functions correctly and efficiently.
Understanding the Data Model
Data model contains two related tables
- Sales Table: Contains transaction-level data such as revenue
- Producthierarchy Table: Stores product details like volume
Both tables are connected using Product ID (one-to-many relationship).
Product table
\rightarrow One side
Sales table\rightarrow Many side
Why RELATED is Required
- Revenue exists in the Sales table, while Volume exists in the Product table.
- Since they are stored in different tables, they cannot be multiplied directly in a simple formula like:
Sales[Revenue] * Product[Volume]
- This is where RELATED() is used. It fetches the corresponding value from the related table during row-level calculations.
Cross-Table Calculations with SUMX and RELATED
To calculate Total Revenue weighted by Product Volume, follow these steps:
Step 1: Create a New Measure
In Power BI Desktop:
- Right-click in the Fields pane
- Select New Measure
- Name the measure Revenue Volume
Step 2: Define DAX Formula
- SUMX iterates over each row in the Sales table.
- sales[Revenue] gets revenue for each transaction.
- RELATED(producthierarchy[Volume]) retrieves the corresponding volume from the Product table.
- The results are multiplied row-by-row.
- Finally, SUMX adds all calculated values together.
Power BI automatically uses the existing relationship to match each Sales row with the correct Product row.
Using the Measure in Report Visuals
- Use the created measure in tables, cards or charts
- Values update dynamically based on filters and slicers
- Displays total revenue adjusted by product volume
- Helps compare performance across products, categories, or time periods
- Ensures calculations remain consistent with table relationships
- Provides deeper and more accurate business insights
When to Use the RELATED Function
Use the RELATED function to retrieve values from a connected table during row-level calculations.
- A valid and active relationship exists between the tables in the data model.
- Calculated columns require data from a related dimension table.
- Iterator functions like SUMX or FILTER are used for cross-table calculations.
- A star schema model requires fetching values from the one-side table to the many-side table.
Advantages
The RELATED function simplifies cross-table calculations by leveraging existing relationships in the data model.
- Retrieves related table values efficiently without merging tables physically.
- Improves performance compared to manual lookup methods like LOOKUPVALUE.
- Maintains data model integrity by using defined relationships.
- Enables clean and readable DAX formulas for row-level calculations.