Relationship
Evaluates a table expression in a context modified by the given filters
RELATEDTABLE(<tableName>)
Comprehensive Guide to the RELATEDTABLE DAX Function
Overview
The RELATEDTABLE function is the complement to RELATED and is essential for aggregating data from the "many" side of a relationship when you're working in the "one" side. It returns a table containing all related rows, which can then be used with iterator functions to calculate totals, averages, counts, and other aggregations.
Applies to: Calculated columns, Calculated tables, Measures
Works in: Row context (typically in dimension tables on the "one" side)
Syntax
RELATEDTABLE ( <table> )
Parameters
Parameter | Required | Description | Example |
table | Yes | The name of the related table from which you want to retrieve rows | FactSales, FactOrders, FactInventory |
Return Value
Returns a table containing all related rows from the specified table based on the current row context.
Understanding How RELATEDTABLE Works
The Relationship Direction Concept
RELATEDTABLE works by following existing relationships in the opposite direction from RELATED:
RELATEDTABLE travels from the ONE side → to the MANY side
Example:
DimProduct (One) ←────────── FactSales (Many)
ProductKey ProductKey
In DimProduct, you can use RELATEDTABLE to get all FactSales rows
Visual Representation:
RELATED vs RELATEDTABLE Comparison
Aspect | RELATED | RELATEDTABLE |
Direction | Many → One | One → Many |
Used in | Fact tables | Dimension tables |
Returns | Single value | Table of rows |
Example | Get product name in sales | Get all sales for a product |
Common use | Calculated column | Measure with iterator |
Basic Examples Using Contoso Data
Example 1: Total Sales per Product
Example 2: Count of Transactions per Customer
Example 3: Average Sale Amount per Product
Multi-Level Relationships (Chained RELATEDTABLE)
You can use RELATEDTABLE through multiple relationship levels.
Example 4: Total Sales per Category
Advanced Scenarios
Example 5: Complex Aggregation with Filtering
Example 6: Calculate Percentage of Total
Example 7: First and Last Purchase Date
Example 8: Product Performance Metrics
Example 9: Customer Segmentation
Example 10: Time-Based Analysis
RELATEDTABLE with Different Iterator Functions
Handling Empty Results
Common Patterns and Use Cases
Performance Considerations
Common Errors and Troubleshooting
RELATEDTABLE vs Other Functions
RELATEDTABLE vs ALL(FactTable)
Best Practices Summary
Quick Reference Templates
Template 1: Sum Aggregation
// In DIMENSION table
Total [Metric] =
SUMX(
RELATEDTABLE( FactTable ),
FactTable[Amount Column]
)
Template 2: Count
// In DIMENSION table
Count of [Items] =
COUNTROWS( RELATEDTABLE( FactTable ) )
Template 3: Average
// In DIMENSION table
Average [Metric] =
AVERAGEX(
RELATEDTABLE( FactTable ),
FactTable[Amount Column]
)
Template 4: With Filter
// In DIMENSION table
Filtered [Metric] =
SUMX(
FILTER(
RELATEDTABLE( FactTable ),
FactTable[Condition Column] = "Value"
),
FactTable[Amount Column]
)
Template 5: Date Range
// In DIMENSION table
[Metric] Last 30 Days =
SUMX(
FILTER(
RELATEDTABLE( FactTable ),
FactTable[Date] >= TODAY() - 30
),
FactTable[Amount Column]
)
Template 6: Multiple Calculations
// In DIMENSION table
Combined Metric =
VAR Related = RELATEDTABLE( FactTable )
VAR Total = SUMX( Related, FactTable[Amount] )
VAR Count = COUNTROWS( Related )
VAR Average = AVERAGEX( Related, FactTable[Amount] )
RETURN
Total & " | " & Count & " | " & FORMAT( Average, "$#,0.00" )
Summary
The RELATEDTABLE function is essential for aggregating fact data at the dimension level:
- Follows relationships from One → Many side automatically
- Works in dimension tables (One side of relationship)
- Returns a table of all related rows
- Must be used with iterator functions (SUMX, COUNTROWS, etc.)
- Enables powerful aggregations at dimension grain
Key Takeaway: RELATEDTABLE brings the many rows from your fact tables into dimension-level calculations, enabling you to create comprehensive analytics, KPIs, and metrics that answer questions like "What's the total for this product?" or "How many times did this customer buy?"
Remember:
- In Dimension (One) → Use RELATEDTABLE → Get Many rows → Aggregate them
- In Fact (Many) → Use RELATED → Get One value → Use directly
Master RELATEDTABLE alongside RELATED, and you'll have complete control over navigating relationships in your Power BI data model! 🎯