Well I've managed to come up with something that works (though there is a little quirk) - but it certainly wasn't obvious. It's a cool trick, though. Maybe someone else can come up with an easier way?
This is what I did.
1) I set up the sheet as per your example, with quick filters on State and a sub-Category.
2) I defined Table Calculations to work out the overall number of distinct Sub-Categories in the data selected by the filters and also the number of distinct Sub-Categories for each Customer. I used the TCRL Distinct Count Calculation which I suggested a few weeks ago for these fields.
3) I defined another Table Calculation which returns TRUE or FALSE depending whether or not the Customer has the same number of Sub-Categories as the overall selection.
4) I defined a filter on that last calculated field, selecting only rows for which the value = TRUE.
The quirk I referred to is that this only works if all selected Sub-Categories exist within the selected State(s). If you select a Sub-Category which doesn't exist in the chosen State, the overall count will be lower, so the result will actually be all rows for Customers who have all selected Sub-Categories which exist in the chosen State. I don't think there are any examples like this in the Superstore data.
What I tried to do to get around this was to define the filter on Sub-Category so that it shows "Only Relevant Values". That would mean that you can't select Sub-Categories that don't exist in the chosen State. But when I selected that, the quick filter dialog didn't show any Sub-Categories at all (see screen shot). This looks like a bug.
Richard, this is very neat and it does exactly what I was looking for thanks. It's actually a surpirisingly useful analysis and I can't imagine why I haven't tried to use it before.
What I have realised as a result of looking at you handiwork, is just how critical the sequence of events is when building table calcs, as I managed to break the sheet multiple times when I first started to play around with it.
Does someone know of any reading/examples that might help turn my current tactics of trial and error into something more logical?
Incidentally I had exactly the same problem as you reported when trying use 'relevant values' in the secondary quick filter.
I am slowly building a library of short tutorial videos about Tableau calculations and other combination of features in Tableau at http://joemako.tumblr.com/
I welcome your feedback and recommendations for content to cover.
Joe, the videos are spot on - good topics and the right length too.
Any chance you could you post the .twbx up there as an accompanyment to future videos?
I'm sure I speak for a few mere mortals out here who would definitely benefit from the opportunity to do some forensic examination of your work.
BTW was it your dog I heard trying to get in on the act? I guess he was just warning you about a missing END statement! 8-)
Yes, that was my dog :)
These videos are me learning how to make tutorial videos, so I plan to get a real microphone and do some editing instead of a just a single take.
For the videos that are there, if you follow the link to the forum post, you can find the workbooks that I used. In the future, after a redesign and re-record of the content, I will keep the videos and packaged workbooks close to each other.
Thank you very much for your feedback.
Hello, I'm curious. Has anyone made new progress on this ? The work-around above is kludgy & doesn't solve all use cases.
I've submitted the new idea: