Tableau Sets and LoD’s

During one of my talks at Tableau Conference 2018 I stated that

“sets are level of detail (LoD) expressions that return 1 or 0, True or False”

In this short blog post I will present some parallels between LoD’s and sets. I hope this enables you to fully leverage sets in your analysis.

Let’s start by looking at an LoD calculation that returns True or False if a State’s Sales value is greater than $100k.

You can get the exact same result if you define a set from the State field and condition that set as shown below.

In both cases, Tableau aggregates Sales at the State level, and returns a sales value per State. That value is then compared to 100k and if greater, the State is in the set. This classification is eventually reflected in every row in the workbook’s data source. Pretty cool, right?

Let’s now see how we can leverage a set inside an LoD expression.

LoD defined by a set

We will kick this off by defining a Sub-Category set with 5 members and then use that set inside an LoD expression to find which States have recorded Sales pertaining to any of these Sub-Category values.

This expression looks at every distinct State value and assesses if there is at least one Sub-Category value in the set that was sold in said State. If so, the value of this calc is True. For example, all Sub-Categories in the set are sold in Alabama and, therefore, the above LoD condition returns True for Alabama.

It is worth showing that the condition also returns True for Montana, although Montana only has Sales for a subset of the Sub-Category values in the set.

You may be wondering why.

MAX([Sub-Category Set]) looks at every row per State and if there are Sales for at least one Sub-Category set value, it returns true for all rows with that State value (In/Out translate to 1/0).

Now that we saw how sets can be used inside LoD’s, it should be pretty easy to use a set to define another set, right?

Set defined by another set

We will get the same result if we create a set from the State field and condition it with the max expression. The max expression will be evaluated per distinct State value, just like the LoD above.

If instead we want to find all states that sell proper sets of Sub-Category values in the Sub-Category set, all we have to do is change the condition from MAX to MIN. As a result, neither Alabama nor Montana will be in the State set.

On the other hand, North Dakota will remain a member of the State set. All 4 Sub-Categories for that State are exclusively 4 of the 5 members in the Sub-Category set.

What about finding States that sell exactly all 5 Sub-Category values in the set (no more, no less)? In addition to checking the Sub-Category value relationships between a State’s Sub-Categories and the set’s, we also need to check that the distinct count of Sub-Categories at the State level is equal to the distinct count of Sub-Category values in the Sub-Category set.

The conjunction’s second facet checks the distinct count of Sub-Categories per State and evaluates whether that is the same as the distinct count of set members in the Sub-Category set.

This expression can also be written as a set condition.

Notice how we removed the Fixed [State] part and instead used Include. Because the set’s LoD is State, fixing the calculation is redundant. We can simply include the set’s dimensionality (State) as the LoD’s scope.

Sets help you discover relationships between fields in our data source. With sets you can compare values across different levels of detail and get to insights faster. I hope this post helps you adopt a “sets mindset.”