Cross-Table Operations - FILTER, SUMMARIZE & Aggregations

AXEL's relationship functions shine when combined with FILTER, SUMMARIZE, and aggregation functions.

Aggregations with RELATED

SUMX with RELATED

SUMX(Table, RELATED(RelatedTable[Column], VIA(field)))

Example:

SUMX([Products], [price] * RELATED([Categories][discount_rate], VIA(category)) / 100)

COUNTX with RELATED

COUNTX([Products], IF(RELATED([Categories][name], VIA(category)) = "Electronics", 1, BLANK()))

Handling Orphans

LEFT JOIN (default): Returns NULL, excluded from SUM/AVG
INNER JOIN: Skips the row entirely

SUMX([Orders], [amount] * RELATED([Customers][multiplier], VIA(customer), JOIN: INNER))

FILTER with RELATED

Filter a table based on values from related records.

FILTER(Table, RELATED(RelatedTable[Column], VIA(field)) = "value")

Examples

-- Filter by region
FILTER([Orders], RELATED([Regions][name], VIA(customer, region)) = "North America")

-- Multiple conditions
FILTER([Orders],
  RELATED([Regions][name], VIA(customer, region)) = "North" &&
  RELATED([Customers][tier], VIA(customer)) = "Premium"
)

-- With DEFAULT for orphans
FILTER([Orders], RELATED([Regions][name], VIA(customer, region), DEFAULT: "Other") = "Other")

SUMMARIZE with RELATED

Group and aggregate using related fields.

SUMMARIZE(Table, RELATED(RelatedTable[Column], VIA(field)), "Measure", Aggregation)

Revenue by Region

SUMMARIZE(
  [Orders],
  RELATED([Regions][name], VIA(customer, region)),
  "Total Revenue", SUMX([Orders], [amount]),
  "Order Count", COUNTX([Orders], 1)
)

Multi-hop Grouping

SUMMARIZE(
  [Orders],
  RELATED([Departments][name], VIA(product, category, department)),
  "Revenue", SUMX([Orders], [amount])
)

Use DEFAULT for Better Grouping

SUMMARIZE(
  [Orders],
  RELATED([Regions][name], VIA(customer, region), DEFAULT: "Unassigned"),
  "Revenue", SUMX([Orders], [amount])
)

Quick Reference

Next Steps

• Cross-Table Advanced - Complex patterns and real-world scenarios
• VIA Clause
• RELATEDTABLE