Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Hi @rmcpdias,
Is your issue solved?
It works well in my environment:
Best Regards,
Link
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
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.
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
Proud to be a Super User!
Paul on Linkedin.
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!
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.
Which "ghost results"? The way the model is set up, the slicer hierarchy works
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.
@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.
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |