RELATEDTABLE - Working with Multiple Related Records
While RELATED retrieves a single value from a related record, RELATEDTABLE returns an entire table of related records. Essential for one-to-many relationships.
Syntax
RELATEDTABLE(TableName)
RELATEDTABLE(TableName, VIA(field))
RELATEDTABLE(TableName, VIA(field1, field2))
RELATEDTABLE(TableName, JOIN: INNER)
RELATED vs RELATEDTABLE
| Aspect | RELATED | RELATEDTABLE |
|---|---|---|
| Returns | Single column value | Entire table of records |
| Relationship | Many-to-one (child → parent) | One-to-many (parent → children) |
| Use case | Get customer name from order | Get all orders for a customer |
| Direct use | Can use directly | Must use with aggregation |
| DEFAULT support | Yes | No |
Basic Usage
Count Orders per Customer
ADDCOLUMNS(
[Customers],
"Order Count", COUNTX(RELATEDTABLE([Orders], VIA(customer)), 1)
)
What Happens:
- For each Customer, AXEL finds all Orders where
customerfield links to that customer - Returns those orders as a table
- COUNTX counts the rows
Total Revenue per Customer
ADDCOLUMNS(
[Customers],
"Total Revenue", SUMX(RELATEDTABLE([Orders], VIA(customer)), [amount])
)
Aggregation Functions
COUNTX - Count Related Records
COUNTX(RELATEDTABLE([Orders], VIA(customer)), 1)
SUMX - Sum Values
SUMX(RELATEDTABLE([Orders], VIA(customer)), [amount])
AVGX - Average
AVGX(RELATEDTABLE([Orders], VIA(customer)), [amount])
MINX / MAXX - Find Extremes
MINX(RELATEDTABLE([Orders], VIA(customer)), [order_date])
MAXX(RELATEDTABLE([Orders], VIA(customer)), [amount])
JOIN Option
LEFT JOIN (Default)
Returns empty table if no related records - COUNTX returns 0.
INNER JOIN
Excludes parent records with no related records.
-- Only customers with orders
ADDCOLUMNS(
[Customers],
"Revenue", SUMX(RELATEDTABLE([Orders], VIA(customer), JOIN: INNER), [amount])
)
Best Practices
Always Use with Aggregation
-- Wrong: Returns table, not usable directly
RELATEDTABLE([Orders], VIA(customer))
-- Correct: Aggregate the table
SUMX(RELATEDTABLE([Orders], VIA(customer)), [amount])
Next Steps
- RELATEDTABLE Advanced - Patterns and real-world scenarios
- Cross-Table Operations