RELATEDTABLE Advanced Patterns

This guide covers advanced patterns, real-world scenarios, and performance tips for working with RELATEDTABLE. These patterns are essential for building analytics that aggregate data from one-to-many relationships, such as calculating customer statistics from their orders or product metrics from their sales.

Complete Customer Statistics Dashboard

One of the most common use cases for RELATEDTABLE is building customer analytics dashboards. This comprehensive example calculates multiple KPIs for each customer from their related orders:

ADDCOLUMNS(
  [Customers],
  "Order Count", COUNTX(RELATEDTABLE([Orders], VIA(customer)), 1),
  "Total Revenue", SUMX(RELATEDTABLE([Orders], VIA(customer)), [amount]),
  "Avg Order", AVGX(RELATEDTABLE([Orders], VIA(customer)), [amount]),
  "First Order", MINX(RELATEDTABLE([Orders], VIA(customer)), [order_date]),
  "Last Order", MAXX(RELATEDTABLE([Orders], VIA(customer)), [order_date]),
  "Largest Order", MAXX(RELATEDTABLE([Orders], VIA(customer)), [amount])
)

What this produces: A single table where each customer row is enriched with their complete order history statistics - enabling you to identify top customers, recent activity, and spending patterns.

Multi-Hop RELATEDTABLE

VIA works with RELATEDTABLE just like it does with RELATED, allowing you to traverse multiple relationship hops to collect related records from distant tables.

Get All Orders for a Department

This example traverses backwards through the relationship chain: Department → Categories → Products → Orders

ADDCOLUMNS(
  [Departments],
  "Total Orders", COUNTX(
    RELATEDTABLE([Orders], VIA(department, category, product)),
    1
  )
)

How it works:

1. Starting from each Department, AXEL finds all Categories with that department
2. For each Category, it finds all Products
3. For each Product, it finds all Orders
4. COUNTX counts the total Orders found

This lets you report on Orders at the Department level, even though Orders don't directly link to Departments.

Common Patterns

Count with Condition

Filter the related records before counting to get specific segments:

-- Count only high-value orders (over $100)
COUNTX(
  FILTER(RELATEDTABLE([Orders], VIA(customer)), [amount] > 100),
  1
)

When to use: Segmenting customers by behavior - "How many premium orders has this customer placed?"

Check if Related Records Exist

Create boolean flags for quick filtering and display:

ADDCOLUMNS(
  [Customers],
  "Has Orders", IF(
    COUNTX(RELATEDTABLE([Orders], VIA(customer)), 1) > 0,
    "Yes",
    "No"
  )
)

When to use: Identifying active vs inactive customers, products that have sold vs unsold inventory.

Percentage of Total

Calculate what percentage of overall business each customer represents:

ADDCOLUMNS(
  [Customers],
  "Revenue", SUMX(RELATEDTABLE([Orders], VIA(customer)), [amount]),
  "% of Total", DIVIDE(
    SUMX(RELATEDTABLE([Orders], VIA(customer)), [amount]),
    SUMX([Orders], [amount])
  ) * 100
)

What this shows: Customer concentration risk - if one customer represents 50% of revenue, that's a business risk worth knowing.

Year-to-Date (YTD) Metrics

Combine RELATEDTABLE with FILTER for time-based analysis:

ADDCOLUMNS(
  [Customers],
  "YTD Revenue", SUMX(
    FILTER(
      RELATEDTABLE([Orders], VIA(customer)),
      YEAR([order_date]) = YEAR(TODAY())
    ),
    [amount]
  )
)

When to use: Comparing current year performance against lifetime value, seasonal analysis, annual reviews.

Real-World Scenario: Inventory Analysis

Product Performance Dashboard

This comprehensive example builds a complete product performance view from sales data:

ADDCOLUMNS(
  [Products],
  "Units Sold", SUMX(RELATEDTABLE([Sales], VIA(product)), [quantity]),
  "Revenue", SUMX(RELATEDTABLE([Sales], VIA(product)), [amount]),
  "Order Count", COUNTX(RELATEDTABLE([Sales], VIA(product)), 1),
  "Avg Sale Amount", AVGX(RELATEDTABLE([Sales], VIA(product)), [amount]),
  "First Sale", MINX(RELATEDTABLE([Sales], VIA(product)), [sale_date]),
  "Last Sale", MAXX(RELATEDTABLE([Sales], VIA(product)), [sale_date])
)

Business value: Identify best-selling products, slow-moving inventory, and products that haven't sold recently (candidates for markdown or discontinuation).

Category Performance (Nested RELATEDTABLE)

For hierarchical analysis, you can nest RELATEDTABLE calls to aggregate up the chain:

ADDCOLUMNS(
  [Categories],
  "Products", COUNTX(RELATEDTABLE([Products], VIA(category)), 1),
  "Total Revenue", SUMX(
    RELATEDTABLE([Products], VIA(category)),
    SUMX(RELATEDTABLE([Sales], VIA(product)), [amount])
  )
)

How the nested calculation works:

1. For each Category, get all related Products
2. For each Product, get all related Sales and sum their amounts
3. Sum those product totals to get the category total

This creates a roll-up from Sales → Products → Categories.

Find Products with No Sales

Identify inventory that hasn't moved:

FILTER(
  [Products],
  COUNTX(RELATEDTABLE([Sales], VIA(product)), 1) = 0
)

Business use: Dead stock identification, warehouse cleanup, promotional candidates.

Handling Empty Results

RELATEDTABLE can return an empty table when no related records exist. Different aggregation functions handle this differently:

-- COUNTX returns 0 when no orders exist (safe to use directly)
COUNTX(RELATEDTABLE([Orders], VIA(customer)), 1)

-- AVGX returns NULL when no orders exist - you may need to handle it
COALESCE(
  AVGX(RELATEDTABLE([Orders], VIA(customer)), [amount]),
  0
)

Best practice: Use COALESCE to provide default values for aggregations that return NULL on empty sets (AVGX, MINX, MAXX).

Aggregation Behavior Summary

Performance Tips

Use LET for Multiple Metrics

When calculating multiple metrics from the same related table, use LET to fetch the records once:

-- Inefficient: Fetches Sales 3 times
ADDCOLUMNS(
  [Products],
  "Revenue", SUMX(RELATEDTABLE([Sales], VIA(product)), [amount]),
  "Units", SUMX(RELATEDTABLE([Sales], VIA(product)), [quantity]),
  "Count", COUNTX(RELATEDTABLE([Sales], VIA(product)), 1)
)

-- More efficient: Fetches Sales once with LET
ADDCOLUMNS(
  [Products],
  LET(
    sales, RELATEDTABLE([Sales], VIA(product)),
    "Revenue", SUMX(sales, [amount]),
    "Units", SUMX(sales, [quantity]),
    "Count", COUNTX(sales, 1)
  )
)

Why this matters: RELATEDTABLE performs a lookup for each row. Using LET caches the result, reducing redundant lookups when you need multiple aggregations.

Filter Parent Table First

When you only need metrics for a subset of parents, filter before adding columns:

-- Better: Filter first, then calculate
ADDCOLUMNS(
  FILTER([Products], [status] = "Active"),
  "Revenue", SUMX(RELATEDTABLE([Sales], VIA(product)), [amount])
)

-- Worse: Calculate for all, then filter
FILTER(
  ADDCOLUMNS(
    [Products],
    "Revenue", SUMX(RELATEDTABLE([Sales], VIA(product)), [amount])
  ),
  [status] = "Active"
)

Performance impact: The first approach calculates Revenue only for active products. The second calculates for all products, then discards inactive ones.

Next Steps

• RELATEDTABLE Basics - Core syntax and simple examples
• Cross-Table Operations - FILTER and SUMMARIZE with RELATED
• VIA Clause - Relationship path traversal in depth