Resolve Many to Many relationship Joins issue via Incorta Virtual Joins

nikhil_cr — Tue, 15 Sep 2026 09:37:39 GMT

Goal:
I have a CustomerGroup table where a customer can appear multiple times (one row per Concept per time period), so customer_code is not unique in this table. I want users to filter by Concept on a dashboard and have that filter apply to our sales-orders table (SL_CustomerOrders) - showing only sales for customers who belong to the selected Concept - without duplicating or losing any sales rows.

Schema setup:

Customer (master table, unique key: Code + Company)
CustomerGroup - Child of Customer, joined on CustomerGroup.Code = Customer.Code AND CustomerGroup.Company = Customer.Company. Multiple rows per customer (one per Concept/period).
SL_CustomerOrders - Child of Customer, joined on SL_CustomerOrders.Customer = Customer.Code AND SL_CustomerOrders.Company_Customer_Key = Customer.Company.

So both CustomerGroup and SL_CustomerOrders are Children of the same Parent, Customer.

What I tried:
I want to filter Concept (from CustomerGroup) and have it cascade to SL_CustomerOrders via Customer as the bridge, without joining CustomerGroup directly to SL_CustomerOrders (that caused sales duplication when tried via MV/SQL join).

I set CustomerGroup as the Base Table on the insight, but the Query Plan Viewer shows SL_CustomerOrders completely disconnected from Customer/CustomerGroup in the plan - [attach query plan screenshot].

I then tried switching the Base Table to SL_CustomerOrders instead, expecting Incorta to traverse SL_CustomerOrders → Customer → CustomerGroup for the filter, but i end up with same result: concept from CustomerGroup as filter has no effect.

Question:

Is Base Table the right mechanism for this "shared parent, filter cascades to sibling child table" pattern, or do I need a different approach (e.g., a bridge/junction table, or restructuring the joins)?
Is there a known limitation with propagating filters between two Child tables that share a common Parent, when neither is unique on the join key?
Any working examples of a similar "dimension used purely as a filter source for a sibling fact table" pattern would be really helpful.

Happy to share more schema/query plan screenshots if useful.

topic Resolve Many to Many relationship Joins issue via Incorta Virtual Joins in Data & Schema Discussions

Resolve Many to Many relationship Joins issue via Incorta Virtual Joins