Dynamic Slicer Values

[Anonymous]

I have a report I am using to sumarize the hightlights from three tables of data.

I wanted to be able to slice the data for all three tables at the same time and seeing as they have common values I have set them up in a relational model:

All three tables have 2 columns in common which are used to form 2 unions. There is a 'Set' number and a 'Type' code.

This works well and with two slicers I can slice by 'Set' and 'Type' and it functions as expected.

However, not all 'Types' are applicable to all 'Sets' so the slicers show values for types that do not exist in certain sets.

So the the problem I am having is how to make the slicer values change dynamically change based upon another slicer's selection.

For example, Set 1 only has types ABC1, DEF and ABC2 however the slicer shows ALL the types from the other sets as well and not just the ones which are pertenant to Set 1.

Likewise, Set 3 only has types AB1, AB2, AZ1, AZ2, DEF but show ALL the types:

Basically when I select a 'Set' from the slicer I'd expect the 'Type' to dynamically change to show only the types that are related to that 'Set'. Currently all types are showing for all sets.

Is there a way that the 'Type' slicer can change dynamically to show only the types that are relevant to the selected 'Set'?

Any pointers would be most appreciated.

PS I have a sample pbix prepared with the relationships formed and an example slicer with sampled data but couldn't see a way to upload it.

[PaulDBrown]

@Anonymous 

To share the PBIX file (very helpful!!), upload it to a cloud service (Onedrive, Google Drive, Dropbox...) and share from there

[Anonymous]

Thanks for the reply. @PaulDBrown 

I've uploaded a sample pbix file to Dropbox. Link here.

[swise001 · Accepted solution]

@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]

Very helpful, thanks!

[swise001]

@Anonymous 

The slicers are disconnected from each other - and filters will only flow in 1 direction - based on your model design.    You can consider a few options: 

1.  Try using bidirectional relationships to push changes from your changed data tables to the other slicer.  This may lead to ambiguity in your model though - since everything is linked.   

2.  Create a measure with CROSSFILTER - and apply it as a visual filter to your Slicers. 

Also you can share a link to your PBIX file (if you put it up in the cloud). 

[Anonymous]

Thanks for the reply.

CROSSFILTER sounds like it might be the way to go, but I'm unsure how to structure this to add it into a measure.

I've uploaded a sample pbix file to Dropbox. Link here.