I have 2 slicers, slicer one has 800,000 values in it, slicer 2 has 150 records.
The is a relationship between the two.
I want to be able to be able to either:
select on slicer 1, and have it filter only the Relevant Slicer 2 values
OR
select on slicer 2, and have it filter only the Relevant Slicer 1 values
Ideally, the interface would allow to search either way without having to choose if selecting from Slicer 1 or Slicer 2. I also want them to be able to clear both selections to return to an initial state where they can select either way, something like if selection is made in slicer 1 , turn slicer 1 to single select and filter other slicer or in reverse.
I have thought of creating a separate page bookmarked that once you that has:
page a: Single select Slicer 1 filtering all Slicer 2
page b: Single Select Slicer 2 filtering all slicer 1.
But this leads them down the having to choose which way they slice.
NB, they must only be able to select 1 value from each slicer.
Because of the volume of of data in Slicer 1, not sure how to best approach this.
I think with that enormous volume of data in your slicers, this becomes a data modeling problem not a reporting problem. My strong inclination would be to denormalize your dimension tables, such that the values for slicer 1 and slicer 2 are in the same dimension table. This may require developing a composite key to properly link the new dimension table to the fact table, but I think likely will perform better than having one slicer filter the other via measures, such as in this article:
The thing I am still wondering about is they both need to be single select, but by doing so, i imagine it prefilters the other slicer. I need some way to have a blank until a selection is made in the non selected slicer.
The use case is
If I select an asset ( slicer 1, n = 800,000), filter slicer 2 to show my only the risk assessments types ( one asset can have multiple types), then make a selection.
This displays item level data for the asset + risk assessment combo.
This could also work in reverse, one risk type, only applicable assets.
As it is item level data, whenever there is multiple selections (eg 2 assets) this state is meaningless and doesn’t display any data. The same would go for 2 risk types.
I am wrestling with the if have already select an asset and type, and want to switch to another asset, the user would have to deselect and select. If slicer 2 remains selected, this will limit the choices available in slicer 1 (or not all assets selectable) which will frustrate users.
Equally, if the user wants to switch searching by assets to risk assessment type after having a first selection, not all risk assessments types would be available.
I was thinking you can have an additional search by slicer containing (Asset first, Risk assessment type first) then dynamically switching the values in slicer 1 and 2 with single select in both slicers… but I don,t know if that would work.
Failing that I could have a reset button bookmark, after each search, resetting slicers back to blank.
Are custom visuals a possibility for you? If so, I would highly recommend taking a look at OKViz’s SmartFilter Pro if you haven’t already. It’s highly optimized for performance, has far better search capabilities that native slicers, built-in reset buttons, cross-filtering, boolean filtering and some other features that may help you meet this requirement really well. In addition, they’ve got a lot of cool formatting options that look slick in a report.
Just closing this one out. I found that from a UX perspective, the biggest improvement was to turn on the Multi select using CTRL which stopped most accidental multi selects by users
Brian, a good suggestion will try it out.