Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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):
Solved! Go to 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:
Proud to be a Super User!
Paul on Linkedin.
@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!
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:
Proud to be a Super User!
Paul on Linkedin.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |