Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Bruin87
Frequent Visitor

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

 

Data Modelling Problem.jpg

1 ACCEPTED SOLUTION
PaulDBrown
Community Champion
Community Champion

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.

model.jpg

 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:

fs.jpg

and you get:

FS.gif

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

2 REPLIES 2
akhilduvvuru
Helper III
Helper III

@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
Community Champion
Community Champion

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.

model.jpg

 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:

fs.jpg

and you get:

FS.gif

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.