The Boolean data type is the most efficient for Tableau to process. Yet it is very under-used.

It seems many developers do not make proper use of the Boolean data type in Tableau. Perhaps it is misunderstood.

This article will tell you what you need to know about the Boolean in a Tableau context.

On various Tableau forums I see developers, from Junior to Zen Master, using a long-winded formula when a simple Boolean would do the same job faster.

This post is to encourage developers to use more Boolean!

Although this is written for Tableau, the exact same also applies to Excel, should any Excel developers read this article.

**What is the Boolean data type?**

In Tableau, Boolean is either True or False. It can only be one of those values. In some software a tri-state data type – True, False, Null – can sometimes be mistakenly referred to as boolean, for example a SQL bit data type.

In terms of performance, Boolean is as good as it gets.

True or False is also represented as 1 or 0, where 1 is True and 0 is False. (Note in some software True is represented as -1.)

This data type is the quickest for computers to process. Using Boolean values in calculated fields gives faster Tableau performance compared to numeric or string calculations.

**Examples of using Boolean in a Tableau calculation**

Perhaps it’s the terminology of True or False that deters people from using it. But another, and perhaps less intimidating way to view it is as a simple Yes or No.

For example, maybe you only want to see profitable stores. Framing this as a question in plain English helps define the Boolean calculation:

“Is profit greater than zero?” – “Yes” or “No”

In Tableau that becomes the boolean calculation:

`SUM([Profit])>0`

The result being True (or Yes) if it is profitable.

However, what I often see is that written:

`IF SUM([Profit])>0 THEN "Profit" ELSE "Loss" END`

Which is actually significantly longer and more complex to write…but seems easier for most developers to understand that over a simple Boolean.

#### Boolean example 2

Another example, create a boolean field called [isProfitable]: (Note you don’t set the data type, Tableau does this automatically.)

`[isProfitable]: SUM([Profit])>0`

Some would then use this as follows:

`IF [isProfitable]=True THEN "Profit" ELSE "Loss" END`

The “=True” is not needed. Remove it and the result is the same.

Think about the IF statement:

*IF something is true THEN do something ELSE do something else.*

The first part of the IF statement is only deriving if something is True or False. Therefore, the =True is unnecessary, it’s implicit in the IF statement anyway.

#### Boolean Example 3

Another example I saw recently:

`IF [Field] = "A" THEN True ELSE False END`

Again, overly complicated. The simple boolean equivalent is:

`[Field] = "A"`

It either does equal A, so returns True, or it doesn’t equal A, so returns False.

#### Boolean Example 4

Similarly, I saw this:

`IF [task] = 'Finish' THEN 1 ELSE 0 END`

Far simpler to write:

`INT([task] = 'Finish')`

#### Boolean example 5

Another example (copied directly from a forum):

`If CONTAINS([Field],"2-Wheel Drive, Front") = true then 1 end`

Which is the same as:

`INT(CONTAINS([Field],"2-Wheel Drive, Front"))`

(Ok, it’s not quite the same; the initial formula returns NULL if it doesn’t contain “2-Wheel Drive, Front”, while the boolean version returns 0.)

**Converting Boolean to a number**

In some of the previous examples, notice I wrapped the Boolean output in the INT() function.

The output of a Boolean is True or False. The INT function converts True to 1 and False to 0.

This is very useful. For example, use this to solve the common error message “Cannot use Boolean type in IF expression“

### Use Boolean for Year on Year, Month on Month, Period vs Period calculations

Another good example is to calculate this year and last year numbers.

Consider a data set such as (deliberately small for the example):

Date | Sales | Profit |

01/01/2018 | 363 | 7 |

01/04/2018 | 979 | 6 |

01/07/2018 | 364 | 9 |

01/10/2018 | 188 | 5 |

01/01/2019 | 719 | 6 |

01/04/2019 | 416 | 1 |

01/07/2019 | 767 | 2 |

01/10/2019 | 376 | 2 |

01/01/2020 | 142 | 8 |

01/04/2020 | 758 | 2 |

01/07/2020 | 146 | 8 |

01/10/2020 | 667 | 3 |

Assume the current year is 2020 and we want to compare this year with last year. That means identifying this year and identifying last year (2019).

We can use Boolean fields for this by creating simple flags, to use in other calculations.

For example the current year (isCY = is Current Year):

`[isCY]: YEAR([Date]) = YEAR(Today())`

For the prior year:

`[isPY]: YEAR([Date]) = YEAR(Today())-1`

Which returns:

Date | Sales | Profit | isCY | isPY |

01/01/2018 | 363 | 7 | FALSE | FALSE |

01/04/2018 | 979 | 6 | FALSE | FALSE |

01/07/2018 | 364 | 9 | FALSE | FALSE |

01/10/2018 | 188 | 5 | FALSE | FALSE |

01/01/2019 | 719 | 6 | FALSE | TRUE |

01/04/2019 | 416 | 1 | FALSE | TRUE |

01/07/2019 | 767 | 2 | FALSE | TRUE |

01/10/2019 | 376 | 2 | FALSE | TRUE |

01/01/2020 | 142 | 8 | TRUE | FALSE |

01/04/2020 | 758 | 2 | TRUE | FALSE |

01/07/2020 | 146 | 8 | TRUE | FALSE |

01/10/2020 | 667 | 3 | TRUE | FALSE |

These flags are the simple to use to calculate sales for this year:

`[SalesCY]: [Sales]*INT([isCY])`

Or profit for the prior year:

`[ProfitPY]: [Profit]*INT([isPY])`

**How does this work?**

Simple – it multiplies all of the values by either 1 or 0. Therefore they either remain as they are ([Sales]*1 = [Sales]) or they are disregarded ([Sales]*0 = 0).

Therefore the formula for aggregated Year on Year is::

`SUM([SalesCY])/SUM([SalesPY]) - 1`

No need for table calculations or filters, the year on year change is handled within the Boolean calculations.

## Common boolean error messages in Tableau

There are a number of errors involving Boolean that appear regularly.

- Can’t compare boolean and string values
- Can’t compare boolean and integer values

To replicate and resolve these issues, firstly create a boolean calculated field.

To create a boolean calculated field in Tableau, create a new calculated field and enter the formula 1=1.

1 equals 1, so this formula returns True.

To return False alter the formula to 1=0. Any formula returning True or False is a boolean calculated field.

### Can’t compare boolean and string values

To replicate this error, create a new calculated field using the above boolean calculated field. Enter the formula:

`IF [boolean] = "True" THEN "T" ELSE "F" END`

To resolve this error completely remove ‘= “True”‘ from the formula OR remove the quotation marks around the word True.

### Can’t compare boolean and integer values

To replicate this error create a new calculated field using the boolean calculated field. Similar to above, enter the formula:

`IF [boolean] = 1 THEN "T" ELSE "F" END`

To resolve this error either remove =1 OR wrap [boolean] in INT() to convert it to a number – with the first option the better choice.

Now you know how to properly use the boolean data type in Tableau, remember to:

## Use more boolean in your Tableau calculations!

If you want faster and more efficient Tableau workbooks, the recommendation is simple:

Use more Boolean and improve your Tableau dashboard performance!

Some great tips here!! I love them. And they are largely applicable to anything that uses booleans, not just Tableau. I will admit that I tend to write out everything in detail in a calculated field, but only because I’ve observed that many people do not truly understand the nature of booleans. As most of my workbooks are shared for public consumption or will eventually be owned by someone else at work, I write out the full statement just so they understand what it’s doing. Thanks for writing this blog and helping to provide the base knowledge people need–maybe that will allow me to eventually be a bit less wordy 😉

Appreciate the comment Ken. And agreed, this applies to most software. It was actually an Excel developer many years ago who helped me to better understand boolean by shortening my convoluted IF statements!

Yeah those Excel IF statements are painful so any way you can shorten them makes a big difference!

Ken, one interesting thing to do in your workbooks would be to use the Boolean formula and comment the explicit one. This way people will understand them and your workbook will be more performant

[Year] = [Max_Year]

// IF [Year] = [Max_Year] THEN “Yes” ELSE “No” END

I usually use booleans and change aliases so filters and labels (when I need them) show in a more explicit way

Does that affect performance? I would say no because internally it is still a boolean but I want to confirm

Great question, not something I have tested, but I agree with your conclusion; if it’s a boolean “under the hood” then it should perform like a boolean regardless of alias.