Forum Discussion
Related data with cross filters
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!
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 ResultAdd 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.
4 Replies
johnt75Super User
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 ResultAdd 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.
- 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
