Related data with cross filters

[YO_CO]

Hi all,

after 3 hours arguing with ChatGPT/Claude and getting 0 results, perhaps you can help with something I assumed was a simple matter but am struggling.

I have 2 tables:
FactOrders - containins primary order number and various details and values. Each primary order can appear more than once as it has different items:

FactOrders
Order	Item	Region	Value
1	1A	US	100
1	1B	US	110
1	1C	US	120
2	2A	EMEA	200
2	2B	EMEA	210
3	3A	EMEA	300
3	3B	EMEA	310
3	3C	EMEA	320
4	4A	APAC	400
5	5A	APAC	500
6	6A	APAC	600

Links table - containing two columns: The primary order and linked orders.
Please note a primary order can also have 0 linked orders, in which case it will not show in this table, and it can also have multiple linked orders:

Links
Primary Order	Linked To
2	3
2	4
5	6

In this example we see order #2 is linked to 3 & 4, and #5 is linked to #6.
The links only work from Primary to linked, meaning 2 is linked to 3, but 3 is not considered linked to 2.

I wish to create a matrix visual which, when selecting an order on a slicer (can be any order from FactOrders table, linked or no), will show me the selected order and all related orders in a single column followed by different metrics.

For example, when selecting Order #1 I'll get the following visual as it has no related orders:

Order	Item	Value
1	1A	100
1	1B	110
1	1C	120

When selecting order #2 in a slicer I'd get the following results (order 2 + items + all related orders and their items):

Order	Item	Value
2	2A	200
2	2B	210
3	3A	310
3	3B	320
3	3C	330
4	4A	400

when selecting #5, I'll see:

Order	Item	Value
5	5A	500
6	6A	600

when selecting #6, I'll see only it as it's not considered related to anything:

Order	Item	Value
6	6A	600

How would I go about achieving this?

Thought it would be simple, but been struggling with it for the past few days.

Crossjoin won't help here as it's large tables in import mode.

Thank you!

[johnt75 · Accepted solution]

Create a disconnected table for use in the slicer like

Orders for slicer = DISTINCT( 'Fact Orders'[Order ID] )

Create a measure for use as a filter

Order is visible =
VAR CurrentOrder =
    SELECTEDVALUE ( 'Fact Orders'[Order ID] )
VAR ChosenOrder =
    SELECTEDVALUE ( 'Orders for slicer'[Order ID] )
VAR RelatedOrders =
    CALCULATETABLE ( VALUES ( Links[Linked To] ), Links[Order ID] = ChosenOrder )
VAR Result =
    IF ( CurrentOrder = ChosenOrder || CurrentOrder IN RelatedOrders, 1 )
RETURN
    Result

Add this measure as a filter to the matrix, to only show when the value is 1. Use the order ID from the fact table in the matrix.

[YO_CO]

Thanks @johnt75 , that... actually works!

Appreciate it!

Much simpler and more effective than anything AI gave me.

How would you alter this so that if more than one order is selected it will still display only the relevant data?

Currently if more than one is selected it shows nothing.

[johnt75]

You can adapt it to cope with mulitple selected orders like

Order is visible =
VAR CurrentOrders =
    SELECTEDVALUE ( 'Fact Orders'[Order ID] )
VAR ChosenOrders =
    VALUES ( 'Orders for slicer'[Order ID] )
VAR RelatedOrders =
    CALCULATETABLE (
        VALUES ( Links[Linked To] ),
        TREATAS ( ChosenOrders, Links[Order ID] )
    )
VAR Result =
    IF ( CurrentOrder IN ChosenOrders || CurrentOrder IN RelatedOrders, 1 )
RETURN
    Result

[YO_CO]

Wonderful, thank you very much!