Forum Discussion
Solved
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 - conta...
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
Super User
Super UserYou 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
Wonderful, thank you very much!