The first thing that comes to mind to solve this problem is to use a data scaffold. The idea is based on creating rows in your data set for each possible range.
For example, say right now your data is
RowID Name Category Count 1 Joe Birds 5 2 Julie Bees 4 3 Jasper Baboons 3
If you want to have all Category dimensions available, you will need to build a scaffold with all the available Category values.
Cat_Scaffold Birds Bees Baboons
After joining in the scaffold, your data will look like:
RowID Name Category Cat_Scaffold Count 1 Joe Birds Birds 5 1 Joe Birds Bees 5 1 Joe Birds Baboons 5 2 Julie Bees Birds 4 2 Julie Bees Bees 4 3 Julie Bees Baboons 4 3 Jasper Baboons Birds 3 3 Jasper Baboons Bees 3 3 Jasper Baboons Baboons 3
As you can see, you data has ballooned in size, your original number of rows times the number of rows in the scaffold. The benefit is now you have a Category dimension available for every row of your original data.
There are some good resources on scaffolding online:
This post on the forum links to several resources: Data Scaffolding in Tableau
There was also a video at TC18: Solving tough time-based problems with skeleton tables and Tableau Prep - YouTube
To give you an example I used the Superstore data and approached the problem of wanting to be able to see every Sub-Category listed for each name, whether they purchased anything from that category or not.
If I didn't build a scaffold, this is what I would see for Aaron Bergman:
What I want to see is:
Step One: I created a simple table in excel that had all the Sub-Category values.
Sub-Category Value Accessories 1 Appliances 1 Art 1 Binders 1 Bookcases 1 Chairs 1 Copiers 1 Envelopes 1 Fasteners 1 Furnishings 1 Labels 1 Machines 1 Paper 1 Phones 1 Storage 1 Supplies 1 Tables 1
Step Two: I added a connection to that data source
Step Three: I joined the Superstore data to the Scaffold data source as you see below. (NOTE: To create the 1 on the left-hand side, I first clicked 'Add new join clause', chose 'Create a join calculation' and then entered a 1 in the calculation field.):
Step Four: Go to Sheet 1 and Drag Customer Name to Rows
Step Five: Drag Sub-Category (Sheet1) to my Rows. So far so good, now I've got every Sub-Category represented for every Customer Name
Watch what happens if I drag Sales to the Text Marks card, tho:
This is not accurate information. In order to see only the correct Sales figures now that I've added in my scaffold, I will need to create a Calculated Field.
Create a calculated field called True Sales that only returns the Sales figure if the Sub-category value of our original data set equals Sub-Category (Sheet1):
Step Seven: Double-Click True Sales to add to the view
Step Eight: Remove Sum(Sales) from the Measure Values card.
As you can see, we are left with just the accurate Sales information by Customer/Sub-Category, but we also still see all the possible Sub-Category values.
Please let me know if this was helpful!
Scaffold Example.twbx 2.3 MB