Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
97 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |