Forum Discussion
Related data with cross filters
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.
Super UserCreate 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.
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 ResultWonderful, thank you very much!