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
rmcpdias
Advocate I
Advocate I

Slicer filtering another Slicer

Hi, good morning.

 

I have a very simple model consting of 3 tables : Area, SubArea and Machines. Each Machine belongs to a SubArea and each SubArea belongs to an Area.

 

I trying to creat a report using 2 slicers, one for the Area and another one for the SubArea. Each time an Area is selected, it must Filter all the SubAreas belonging to it. The problem is that if I change the Area value, all the SubAreas that belongued to the previous Area and were unselected are shown in the list of SubAreas of the new Area.

 

Does anyone here experienced the same problem?

 

 

Best Regards,

Rui

11 REPLIES 11
v-xulin-mstf
Community Support
Community Support

Hi @rmcpdias,

 

Is your issue solved?

It works well in my environment:

v-xulin-mstf_0-1620114222712.png

 

Best Regards,
Link

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

 

Anonymous
Not applicable

I'll have a look soon. Thanks for posting this. Frankly, I find it hard to believe that what I described above does not work in this case... since It's always worked! Let me poke around a bit.

PaulDBrown
Community Champion
Community Champion

@rmcpdias 

If the model

is set up with seperate dimension tables for Area and Subarea, you should be able to achieve what you need using a simple measure:

Filter slicer = COUNTROWS(RELATEDTABLE(Machines)) and add this measure to each slicer in the filter pane and set the parameter =1

 





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.






Anonymous
Not applicable

@rmcpdias 

 

This happens because you keep Areas and Subareas in two different tables. I don't think this is how it should be in this case. Please move areas and sub-areas to one table and the problem will disappear due to the auto-exists feature (which will then kick-in). Alternatively, you could create a simple measure that would check for each sub-area if there's an entry in the fact table for the currently selected area and hide the rows in the sub-area slicer which do not have any entry. But I think the best solution is to move areas and sub-areas into one table. This makes sense because there seems to be a natural hierarchy with areas and sub-areas.

Hi daxer.

 

I've tested that before posting, and the problem was the same. I've changed to Chiclet Slicer and now it works!

Anonymous
Not applicable

@rmcpdias 

 

I can assure you that if you keep both in the same table, slicers attached to Area and Sub-Area will be cross-filtering each other without any other setup. This is, as I said, due to how auto-exists works. However, for this to work you only have to keep the combinations of (area, sub-area) that are not empty, meaning a combination must have at least one entry in the fact table. If you have empty combinations, they WILL show up. If this is the case, the only other good solution is a simple measure that will filter one of the slicers through the Filter Pane based on the selections made in the other slicer. This could, in fact, work both ways for both slicers at the same time. The solution with a filtering measure does not require keeping the combinations in one table.

Take a look at model:

 

https://1drv.ms/u/s!AmzyDwofiLHinFExd83TORK8CVq3?e=1BNpFI

 

It should work, I know that, but it's always showing those "ghost" results.

 

@rmcpdias 

Which "ghost results"? The way the model is set up, the slicer hierarchy works





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.






Did you try it Paul?

 

When I use it on my computer, everytime I change the Area, all the SubAreas that were not selected are "transported" into the the new Area SubArea's list. And when I click them, they disappear.

amitchandak
Super User
Super User

@rmcpdias can you share a relationship diagram?

If this is from Area ->(1-M) Sub Area ->(1-M) Machine

it should filter.

 

or create a measure in the Area table

A= count(Area [Area]) ,

 

Go to visual level filter or subarea slicer and check that A is not blank

Take a look at model:

 

https://1drv.ms/u/s!AmzyDwofiLHinFExd83TORK8CVq3?e=1BNpFI

 

It should work, I know that, but it's always showing those "ghost" results.

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.