Forum Discussion
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
Anonymous
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.
Anonymous
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.
- v-xulin-mstf
Community Support
Hi Anonymous,
Is your issue solved?
It works well in my environment:
Best Regards,
LinkIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
- amitchandak
Super User
Anonymous 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.
PaulDBrownCommunity Champion
Anonymous
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
