Forum Discussion
Dynamic Slicer Values
Anonymous
Thank you for sharing the file.
As suspected - using bidirectional filters for all 3 filter paths will not work. It introduces ambiguity.
The crossfilter pattern may work for you. Here is a sample formula:
Table1CrossFilter = Calculate(countrows('Table 1'),crossfilter('UNION TABLE ON TYPE'[TYPE],'Table 1'[TYPE],BOTH))When this is introduced as a filter to the Slicer - (where value > 0 ) it will filter the visible slicer results as you want.
The challenge with this method - is determining how to handle situations where either Table1,Table2 or Table3 are empty (such as with set 4.) Using the crossfilter method as displayed will essentially 'blank' out the slicer since it is propagating a blank table to the Type slicer.
To solve this issue - you'll need to introduce some DAX that will conditionally set which tables to propagate the relationship based on whether there are results. So for example - if set 4 is selected - only Table 2 has results - so this is the only relationship that should be propagated to the Type Filter. If all 3 tables have results - than all 3 filters can be propagated.
Hope this helps you get started.
Anonymous
Thank you for sharing the file.
As suspected - using bidirectional filters for all 3 filter paths will not work. It introduces ambiguity.
The crossfilter pattern may work for you. Here is a sample formula:
Table1CrossFilter = Calculate(countrows('Table 1'),crossfilter('UNION TABLE ON TYPE'[TYPE],'Table 1'[TYPE],BOTH))When this is introduced as a filter to the Slicer - (where value > 0 ) it will filter the visible slicer results as you want.
The challenge with this method - is determining how to handle situations where either Table1,Table2 or Table3 are empty (such as with set 4.) Using the crossfilter method as displayed will essentially 'blank' out the slicer since it is propagating a blank table to the Type slicer.
To solve this issue - you'll need to introduce some DAX that will conditionally set which tables to propagate the relationship based on whether there are results. So for example - if set 4 is selected - only Table 2 has results - so this is the only relationship that should be propagated to the Type Filter. If all 3 tables have results - than all 3 filters can be propagated.
Hope this helps you get started.
Very helpful, thanks!