LODs (Level of Detail expressions) are not the easiest topic in Tableau, though they are a glorious one! It took me some time to get the hang of it, but today I don’t want to miss them. What they do – in a nutshell – is move the level of aggregation somewhere that is not controlled by the dimensions we have on Rows, Columns, Filters or the Marks Card. You have a bar chart with Sales by Countries, but you also want to see the numbers by continent beside them? We cannot do that without an LOD.

But hey, we are talking Relationships here, so we will skip the introduction into Level of Detail expressions. There are a few instances in which they are able to help in our data model. The most important one is probably the Save the Nulls use case:

Use Case: Save the Nulls

Remembering the Golden Rule of Relationships (measures retain values), we know a dimension from the left table, Birthdays, and a dimension from the right table, Hobbies, will build an Inner Join. Non-matching rows (nulls) in either table are being dropped. Now, let’s say we add a measure from a Hobbies table (right, see next image). Here, Tableau will build a Right Join so all non-matching records (names in the Birthdays table) will be dropped. But maybe we don’t want this. Maybe we like our non-matching names and want them to show, even if there isn’t a hobby for them. Yes, we don’t have any hobby information that helps with finding a birthday present, but at least we still know when their birthday is.

This is, where we can work with a FIXED LOD:

{ FIXED [First Name] : SUM([Tenure]) }

That LOD moves our measure from the right table into the left table because we fix it onto a dimension from this left table. In other words: our [Tenure] from the Hobbies table has now become [Tenure] from the Birthdays table; and as measures retain values, all names from the Birthdays table are shown in our view.

 

Let’s note that by nature of the Left Join we create here, we lose non-matching records from the Hobbies table. There is no Hobbies measure in the view anymore (Matt vanishes.)

What Else Do I Need to Know?

I have read quite a few times that LODs do not behave differently with Relationships than they did before. While this is technically true, we need to really understand LODs to get what they do in Relationships. Most importantly, we must not forget that every LOD produces a row-level measure. Yes, we aggregate within the LOD; but the whole thing is a row-level calculation, which we can confirm when we drag it into our view. It gets auto-aggregated, as all row-level measures do.

Now, we have learned that row-level calculations (across tables) always produce an Inner Join, while aggregated calculations (across tables) always produce a Full Join. (See Ground Rules: Rule #2.) The same is still true with LODs. They are row-level calculations. If we fix our numbers on a dimension from the left table, then this measure is just that: a simple measure that belongs to the left table. If we drag it into the view, it will behave exactly like any other measure from that table.

Then, if we fix our numbers on dimensions from both tables (in other words, across tables), it is different. This field will not belong to a table but will be sorted into the lower area in our Data Pane as if independent in its origination from either table. As it is still a row-level calculation, its values are a result of an Inner Join of both tables. In this case, with numbers fixed on dimensions from both sides, there is one very strange consequence: The field has no power to retain any values from either table. And that might feel odd because our LOD contains an aggregated measure from one of the tables and should retain the values of that one, right? But no, in terms of joining behavior, the measures in an LOD don’t matter at all. It’s the dimensions that matter in an LOD.

 

I know by now you will have said: How the heck will I remember all that? This is so convoluted that it will leave my brain five minutes later! And I feel you, though, I have good news for you. We can dump everything down into these statements:

• LODs are always row-level measures (we know that).
• LODs that belong to a table behave like any other measure (that’s simple!).
• LODs across tables are sorted into the lower area in the data pane. They don’t belong to a table; they exist outside of either table. Like calculations combining row-level measures from two tables (okay, we heard that rule already).
• Row-level measures across tables submit to the structure of the view. Meaning, they require context from the view, they don’t overrule its structure and they don’t retain values (This is all we have to remember!).

What About INCLUDE & EXCLUDE?

INCLUDE and EXCLUDE work exactly as we would expect, which ironically enough can lead to some confusion. For example, some people expect (and I did as well in the beginning), that if I INCLUDE a dimension from the left table, this would build a Left Join. But no, INCLUDE – as it always has – takes the current dimensions in our view into account, as well. So, if there is a dimension B in the view and we INCLUDE values for a dimension A, then we will still get an Inner Join.

Again, let’s recall from earlier that a dimension from the left table and a dimension from the right table will build an Inner Join together. Our INCLUDE LOD enforces this scenario.

Same thing for EXCLUDE, although that’s easier to understand. I run again with my Inner Join that dimensions from tables A and B, EXLCLUDING dimension B, will produce a Left Join as the dimensions for our calculation now only come from table A.

Please note: Given their dependency on context from the view, INCLUDE and EXCLUDE calculations are dynamic and will always be sorted into the lower area of our data pane on the left. FIXED, as stated before, is only sorted into the lower area if it fixes dimensions across tables.

And What About Nested LODs?

And for nested LODs, again, an LOD is a row-level field. We can use it like any other measure that is aggregated in another LOD. No problem, as long as we know what we are doing, of course. For Relationships, it is pretty simple. All we have to remember is: The outer LOD counts. The one in which everything else is nested determines to which table the whole field belongs (or no table at all). So, we can fix dimensions and measures from table B; but if we nest that into a FIXED LOD for table A, the whole field belongs to table A.

And that’s it! A lot of fun with LODs here. Don’t give up if you are having a hard time with them. Sometimes, it takes a bit longer to get what they are doing. Also, you might check out our blog series that does a deep dive into each of the three LODs, starting here.

And if you have enough of them right now and want to go deeper into Relationships, check out our next post about extracts in Tableau!