Cross-Table Advanced Patterns

This guide covers complex patterns and real-world scenarios for combining AXEL's relationship functions with FILTER, SUMMARIZE, and aggregations. These patterns are essential for building sophisticated analytics dashboards and reports.

Complex Aggregation Patterns

Pattern 1: Filtered Aggregation by Related Fields

One of the most powerful patterns is filtering data based on related field values before aggregating. This lets you calculate metrics for specific segments.

Use Case: Calculate revenue only from Premium customers in the North region.

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

How it works:

1. FILTER iterates through each Order
2. For each Order, RELATED fetches the customer's tier and region
3. Only Orders matching both conditions are kept
4. SUMX then sums the amounts from filtered Orders

Pattern 2: Percentage Contribution by Group

Calculate what percentage each group contributes to the total. This is useful for understanding revenue distribution across regions, departments, or categories.

ADDCOLUMNS(
  SUMMARIZE([Orders],
    RELATED([Regions][name], VIA(customer, region), DEFAULT: "Unassigned"),
    "Revenue", SUMX([Orders], [amount])
  ),
  "% of Total", DIVIDE([Revenue], SUMX([Orders], [amount])) * 100
)

Result:

Pattern 3: Multiple Group-By Columns

Group by multiple related fields to create cross-tabulated reports. This enables analysis across multiple dimensions simultaneously.

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

Result:

Real-World Dashboard Scenarios

Scenario 1: Department Performance Dashboard

Build a comprehensive view of how each department is performing with multiple KPIs.

SUMMARIZE(
  [Orders],
  RELATED([Departments][name], VIA(product, category, department), DEFAULT: "Other"),
  "Total Revenue", SUMX([Orders], [amount]),
  "Order Count", COUNTX([Orders], 1),
  "Avg Order Value", DIVIDE(SUMX([Orders], [amount]), COUNTX([Orders], 1))
)

Result:

Scenario 2: Customer Tier Analysis by Region

Understand how different customer segments perform across geographic regions.

SUMMARIZE(
  [Orders],
  RELATED([Customers][tier], VIA(customer), DEFAULT: "Unknown"),
  RELATED([Regions][name], VIA(customer, region), DEFAULT: "Unassigned"),
  "Revenue", SUMX([Orders], [amount]),
  "Order Count", COUNTX([Orders], 1)
)

Result:

ADDCOLUMNS vs SUMMARIZE

Understanding when to use each function is crucial for building effective reports.

ADDCOLUMNS Example

Keeps all rows, just adds new columns with related data:

ADDCOLUMNS([Orders],
  "Customer", RELATED([Customers][name], VIA(customer)),
  "Region", RELATED([Regions][name], VIA(customer, region)),
  "Department", RELATED([Departments][name], VIA(product, category, department))
)

SUMMARIZE Example

Groups rows and reduces them to unique combinations:

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

Error Handling Best Practices

Handling Orphans in FILTER

When filtering by related values, orphan records (where RELATED returns NULL) behave differently based on how you write the condition:

-- This EXCLUDES orphans because NULL != "North"
FILTER([Orders], RELATED([Regions][name], VIA(customer, region)) = "North")

-- This INCLUDES orphans when you want to find unassigned records
FILTER([Orders],
  RELATED([Regions][name], VIA(customer, region), DEFAULT: "Unassigned") = "Unassigned"
)

Safe Division

Always use DIVIDE instead of / to handle division by zero gracefully:

-- Safe: Returns 0 if count is zero
"Avg Order", DIVIDE(SUMX([Orders], [amount]), COUNTX([Orders], 1), 0)

-- Unsafe: Could cause errors
"Avg Order", SUMX([Orders], [amount]) / COUNTX([Orders], 1)

Performance Best Practices

Filter Early, Aggregate Later

Apply filters before complex operations to reduce the data being processed:

-- Better: Filter first, then summarize smaller dataset
SUMMARIZE(
  FILTER([Orders], [amount] > 0),
  RELATED([Regions][name], VIA(customer, region)),
  "Revenue", SUMX([Orders], [amount])
)

-- Worse: Summarize everything, then filter results
FILTER(
  SUMMARIZE([Orders],
    RELATED([Regions][name], VIA(customer, region)),
    "Revenue", SUMX([Orders], [amount])
  ),
  [Revenue] > 0
)

Be Explicit with DEFAULT

Always specify DEFAULT when NULL groupings would be confusing in reports:

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

This creates a clear "No Region" row instead of a confusing NULL row.

Next Steps

• Cross-Table Basics - Core syntax and simple examples
• VIA Clause - Relationship path traversal
• RELATEDTABLE - Working with multiple related records