I've implemented the approach described earlier for making the dashboard work even when no keywords have been filtered. I've set this up by duplicating the original to create a TOTALS data source, which does not have any linking fields in the blending. Since all the blending links are broken, then within this TOTALS data source I've re-named the calculated field from [# of Keywords Selected] to [# of Keywords Total]. This is all vis-a-vis maintain percent of totals whilst filtering.
From here, these are the next steps. Two new calculated fields in the primary data source:
[Keyword Has Been Filtered]:
[# of Keywords Selected] != [TOTALS].[# of Keywords Total]
This will evaluate to TRUE only when at least one keyword is filtered in the dashboard. If no keywords have been filtered yet, then this boolean will evaluate to FALSE.
[Keyword Filter + Intersection]:
IF NOT [Keyword Has Been Filtered]
ELSEIF [Keyword Has Been Filtered] AND [INTERSECTION]
This new field replaces [INTERSECTION] on the filters shelf, to now show only bocce teams where [Keyword Filter + Intersection] = TRUE
In other words:
- If no keywords have been filtered, then show all teams
- If at least one keyword has been filtered AND [INTERSECTION] is true, then the team appears
- If at least one keyword has been filtered & is [INTERSECTION] false, well then the team name is filtered from the view
If Jonathan's original work was on sheets named workout, view, and dashboard; then my new work can be found in sheets named workout 2, view 2, and dashboard 2.
CAVEAT: This is a solution that works. But I don't like it!
It's unattractive because:
- now we're data blending
- and now there's a calculation to always count the distinct number of keywords across the entire data set
with 100,000+ keywords, that's a lot of blending & counting, which means CPU & hourglass
2. It's just clunky:
- As Pankaj observed, this work-around for a work-around shows, again, how much is required to accomplish something which by all reasonable expectations should be rather simple.
From here, it would be great to do the following:
1. Find a better way to make this work when no keywords have been filtered
2. remember to vote for the Multi-Select Filters: INTERSECTION instead of UNION idea
so Tableau can implement this as a simple customization option in their product
I'll come back to look at your Category question next week when there's more time.
Also, I've mentioned before that this is not the only filter in play on my dashboard. In real life, the dashboard already has a large number of other parameter-driven filters whose purpose is to narrow the number of bocce teams down from <500 to just a handful.
While this solution we've identified here works on its own in isolation: it doesn't play nicely with other parameter filters. After adding this config into my real dashboard, now the original parameter filters are each broken unless at least one keyword has been filtered.
Argh! The saga continues ..
@Keith: When I'd created the dashboard, I'd assumed that given the size of the lists of teams & keywords you'd want to hide them if nothing was displayed, so on the Filter Action I set the "Clearing the selection will:" option to "Exclude all values". Switching that back to the default "Show all values" and the keyword list appears again***, but not the view for the problem you outlined with the intersection filter. Besides what you came up with, there are a couple of other potential solutions. One might be to do something in SQL to get you a pre-aggregated total, however that wouldn't work in this situation due to other filters. After playing around with this a bit, I came up with another solution that I think works even better than the data blend.
*** If you are using the keyword list, you should probably do something on that keyword list to restrict the size, so it's not trying to build out a string with 100K keywords.
When there are no keywords selected i.e. return all teams for every keyword, then the [# of Keywords for Team] >= [# of Keywords Selected] "Intersection Filter" calc returns False for every team. The only time that ever happens is when there are no keywords selected, every other time there will be at least one team returning True. The "# of Keywords Meeting Intersection" calc has the following formula: WINDOW_SUM(IF [Intersection Filter] THEN 1 ELSE 0 END). This returns 0 when no keywords are selected. Then the new "Intersection w/Show All Values" calc is:
IF [# of Keywords Meeting Intersection] == 0 THEN
ELSE [Intersection Filter]
This will now show all values. The "show all values" and "dashboard" in the attached workbook are set up to demonstrate this.
Also, since all of the calcs have the same Compute Using, for production I'd probably collapse them into a single calc for easier maintenance.
Ok, now for the parameter filters. I'm not sure how you've set the parameter filters up, I'm suspect that the problem is that the secondary data source used for getting the totals is not being filtered by the parameters, so the evaluation is failing. The table calc solution I came up with avoids this, I created a parameter filter as a trial and it works.
@Pankaj: Only Relevant Values is an option only for dimensions and regular aggregate calcs from the primary datasource. In other words, it's currently not available for fields from data blends or fields that use data blends (there's been an enhancement request for that), nor is it available as an option for table calcs, though table calcs do a form of only relevant values, as we shall see. The set of values for relevant values filters is computed in the data source at the end of the regular queries. This particular view is built using a table calc filter, so Only Relevant Values won't work. However, if we make a table calculation for the 1 to 1 dimension that is aware of the table calc filter, then Tableau will only show the available values for that new table calc.
In the "1 to 1 dim TC workout view", I created a dummy 1 to 1 dimension using "x_" + [bocce_team] and brought that into the view as an ATTR(). As an aggregate, by default it won't affect the addressing or partitioning of table calculations. Then I created the following 1 to 1 dim TC filter calc:
LOOKUP(IF [Intersection w/Show All Values] THEN MIN([1 to 1 dimension]) END,0)
This table calc only returns the 1 to 1 dimension when the Intersecion w/Show All Values calc returns true. Therefore, when added to the view as a Quick Table calc it'll only show the set of teams available for the given keyword.
This can result in a bit of an odd circumstance where we could have selected a team via the 1 to 1 dim TC filter, then chosen a keyword that causes that team to not be in the display. Tableau won't show that team in the filter, though it preserves the information internally so if the keyword selection changes, that team can come back.
I've added this calculation to the dashboard.
To sum up, I agree that this is all more difficult than it could be. As users we are definitely taking Tableau into more complex use cases with interaction paths that weren't necessarily in the original design space for dashboards, table calcs, etc. and bumping up against various functional boundaries & limitations of the software. We can certainly vote for product enhancements and advocate for them with other users, through social media, contacts with Tableau staff, etc. (And I'm going to be using this thread as an example of something that can be improved).
Where I start from, though, is that I have a piece of software today, and a specific need today, and given Tableau's 6-12 month release cycle for .x and .0 releases I can't be waiting for them to build my dream functionality. I've come to view those functional limits more like the limits of a haiku or a sonnet, where we can take on the challenge to communicate our ideas within a 5/7/5 structure, or the 1000x800 canvas of a Tableau dashboard.
Hi Jonathan, thanks.
I can appreciate the haiku analogy. Beauty within the limitations of a simple framework is elegant. However, when a construction worker arrives to a job site that requires heavy machinery, but he has brought only a simple toolbox, then the answer is to get an enterprise ready toolset. None of the world's great authors could have written their masterpiece works as a sonnet.
That said =) you're absolutely right. While we're here waiting patiently for Tableau to build out their toolset & voting on ideas at a pace they can't keep up with, we have to work with what we've got. And we have to stay positive & think in terms of being creative.
I've found a problem in your most recent response. And I've also found a utilitarian solution that is good enough, for my purposes at least. As you'll see: it is also imperfect & is no substitute for proper working functionality out of the box. So if you're reading this, please vote.
The flaw with your recent solution is that it works up until it doesn't. As soon as you add enough keywords into the filter so that you've exhausted the options & no team is left that meets the criteria, then the number of teams displayed should be zero. But by suggesting that:
IF [# of Keywords Meeting Intersection] == 0 THEN
you're displaying all teams in this scenario, which is inaccurate. For example, try adding these keywords consecutively into the filter:
3d: eleven teams
awesome: six teams
love: four teams
brain: all teams (incorrect)
OK. So now what ? I'm not happy with the data blending approach. It's just kludgey. So here's my new answer:
If you'll recall: when there's no filter, then we would expect the dashboard to show all teams. But instead of going to zero, the calculation for [# of Keywords Selected] skyrockets upward to the total distinct count of all keywords across the data set. Following in the zen tradition =), I'm not going to fight against this reality but instead go with the flow & work with it.
My new logic for the "Intersection w/Show All Values" filter is as follows:
IF [# of Keywords Selected] >1000 THEN
This will return all teams when no keywords are filtered, and zero teams when none meet the intersection. And I'm baking in the assumption that no user will ever search for the intersection of 1000+ keywords at once. So, I'm pushing the break-point out to an extremely unlikely edge case.
Works for me, and I'm happy with it. Thanks so much for your help, I wouldn't have gotten here without it!