Date Modelling - Filtering a Dimension by another Dimension without bidirectional filtering

[Bruin87]

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):

[akhilduvvuru]

@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!

[PaulDBrown · Accepted solution]

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: