Hot-Swapping Dimensions with LOD Expressions in Tableau

Data

Hot-Swapping Dimensions with LOD Expressions in Tableau

by Rick Thomas
//

During a recent project, I received an interesting question:

“Can we swap what dimension is displayed in a stacked bar chart based on a filter being applied?”

I’ve decided it would be easier for me to answer this question by showing you how to do it with the Superstore data. Below, we have a stacked bar chart displaying percent of total sales by departments within regions:

 Tableau hot-swapping LOD expressions

This looks clean enough and reasonably informative. Now, if we replace Department with Category, we get the following mess of a visualization:

Tableau hot-swapping LOD expressions

Because Department and Category are hierarchical and mutually exclusive, when the visualization is filtered by Department we get something much more manageable:

Tableau hot-swapping LOD expressions

The client wanted to show the bars broken down by department when All is selected. Plus, they wanted to show the categories that belong to a department when filtered by that same department.

Complexity vs. Efficiency 

So, the long, non-efficient way to do this would be to swap-out worksheets in a dashboard using a parameter. This is fine, but overly complicated when there is a quick, efficient solution available using a level of detail (LOD) expression and a context filter!

We are trying to build a calculation based on more than one distinct department displayed in the level of detail view. When you show All departments, the COUNTD([Department]) will be >1 and return Department. Otherwise, it will be =1 and return Category when filtered.

The obvious issue here is the limitation non-aggregated dimension returns in an aggregate statement. However, by using a globally fixed LOD statement wrapped around the aggregate statement of COUNTD([Department]), we can skirt this issue:

Tableau hot-swapping LOD expressions

Now, you would think we could place this calculated field on Color on the Marks card, filter our department and return our categories related to that department, right? Unfortunately, that will not work as you can see below:

Tableau hot-swapping LOD expressions

So, what is going on here and why are we only seeing a single department returned rather than its categories? This is due to the standard order of operations within Tableau:

Tableau hot-swapping LOD expressions

Remember that fixed LOD expressions ignore all filtering by default. However, it will not ignore a filter added to Context! If we add the Department filter to Context, the visualization will now function as desired. When All is selected, we can see sales is broken down by department:

Tableau hot-swapping LOD expressions

And when we select a single department, we get its associated categories:

Tableau hot-swapping LOD expressions

And there you have it! Efficiency and magic produced through LODs! You can see the final viz below. 

More About the Author

Rick Thomas

Analytics Consultant
Hot-Swapping Dimensions with LOD Expressions in Tableau During a recent project, I received an interesting question: “Can we swap what dimension is displayed in a stacked bar chart based on a ...
Chicago Cubs 2016 Season in Review As far back as I can remember, the Chicago Cubs were, to me, family. From my grandfather and father growing up spending summer days ...

See more from this author →

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK

×

Interworks GmbH
Ratinger Straße 9
40213 Düsseldorf
Germany
Geschäftsführer: Mel Stephenson

Kontaktaufnahme: markus@interworks.eu
Telefon: +49 (0)211 5408 5301

Amtsgericht Düsseldorf HRB 79752
UstldNr: DE 313 353 072

×

Love our blog? You should see our emails. Sign up for our newsletter!