Day 13: Blending as a filter

I would argue (and win) that blending is either one of the best or worst features of Tableau.  Sometimes it's brilliant and works exactly how you need it to (such as my current project I'm working on here in Atlanta, which wouldn't be possible without blending) and other times (like when you need to do a row level join between two data sources of different types (and you don't have Alteryx)) it feels half baked and inadequate.  But since I'm a glass half full kinda guy, we're going to look at a trick to leverage blending when your normal filtering doesn't work the way you want it to.

Learn About Tableau

For those of you with much experience in Tableau, I'm going to go ahead and answer the voice inside your head that as you read this will say, "That's not the only way to do that".  That's correct.  There's many ways to do this trick.  There's always many ways to do a trick - Pick a card, any card (see what I mean?).  But this is a simple way to handle tricky filtering.

Here's the set up.  I have population data on all fifty states in the in the US.  I want to show it in a map, but I only want to show the lower 48 (it fills up the window better and who really cares what people it Hawaii think anyway!?!?).  Problem is when I click to filter and then unclick to un-filter to show all, the state filter where I excluded Alaska and Hawaii also disappears and the map returns to show everything, and I'm left with a killer view of the Pacific Ocean right in the middle of my window.  What I want to happen is that my exclusion of Alaska and Hawaii will remain, regardless of the filtering being done as a result of actions on the dashboard.  We have a dashboard that looks like this (showing Alaska and Hawaii in the bar chart but excluded in the map):

So here's what I've seen done before (and then we're going to do a trick): Once you have the visualization(s)/dashboard created, right click on your data source and select duplicate.

Now, in the duplicated data source bring "State" on to the filters shelf of the sheet with the map.  Hit the "Exclude" check box, and check off Alaska and Hawaii.  

Now, the the map should update and show only the lower 48.  Now right click on the filter in the filters shelf and click Apply to Worksheets>>Selected Worksheets, and now check off all the other sheets in the dashboard you wish to apply this filter.

Now when you select a state and then deselect it, you keep only the lower 48.  Pretty neat huh?

"But Nelson, I'm looking at 30 Million rows of data.  Do I really need to duplicate the whole thing?"  Of course not!  Here comes today's trick.  Instead of duplicating the entire primary data source, create a new sheet with just the field you want to do the blend filtering on.  For the state example it would look like this:

Now, select Worksheet>>Copy>>Crosstab.  You now have this data sitting on your clipboard.  Simple hit CTRL+V and Tableau will paste that data into your workbook as a data source - you'll see it up there at the top left, starts with "Clipboard...."

The great thing is how small this data is.  It's only the number of distinct rows in your data (So 50 rows for fifty states :)).  Now, I'll use this clipboard data as my blended data set and follow the same steps as above.  

Now that's a trick.  I've posted two versions of the same dashboard, one using the blended data as the filter, the other with a normal filter, so you can see the issue I described with the normal filter:

Learn About Tableau

Thanks as always for stopping by.  
Nelson