Forum Discussion
Date Modelling - Filtering a Dimension by another Dimension without bidirectional filtering
Hi,
i am having problems solving the task to build a slicer that filters Dimensions A (Business Units) and simultaneously filtering another Dimension B (Location).
So, if I filter the Business Unit to "Air" I only want to see the Locations that are linked to "Air". The information which location belongs to which Business Units should not be derived by the booked data (non-zero), but should be displayed even when values are zero.
I have a "MasterMapping" Dimension that has a row for each possible combination. So if you filter this Master Mapping by Business Unit Air (within this Dimension) you see all the relevant locations. I think this helper table must be the key to the solution but i am struggling how to model it and how to build the slicer without the bidirectional filtering enabled (as I cannot predict the effects on all the other measures I have)
Do you have any tips or links to tutorials?
Thanks in advance
Bruin
The Datamodel (simplified):
You don't need the mapping table. All you need is a measure to use as a filter in both slicers.
In this example, the model is as follows.
The measure to use as a filter is (Ftable is the fact table):
Filter Slicers = COUNTROWS(RELATEDTABLE(FTable))Add the measure to the filters in the filter pane for each slicer and set the value to greater or equal to 1:
and you get:
2 Replies
You don't need the mapping table. All you need is a measure to use as a filter in both slicers.
In this example, the model is as follows.
The measure to use as a filter is (Ftable is the fact table):
Filter Slicers = COUNTROWS(RELATEDTABLE(FTable))Add the measure to the filters in the filter pane for each slicer and set the value to greater or equal to 1:
and you get:
PaulDBrown - I also have similar requirement. However, I want my dimensions interact in Filter Pane (not in slicers). Is there any workaround for that? Thanks!
