IIF in Tableau provide the One-line function to return value based on condition true and false. IIF statement in Tableau is the shorter way to write a IF statement. It is similar the IF function in excel that if first condition is true it will return True value else it will return false value.
IFF() function can be used for any data-types. IIF statement handling unknown values as well, However it is not applicable for IF statement in tableau
Syntax of IIF() function
IIF(<Condition_Test>, return value if condition true, return value if condition false, Unknown value)
IFF function consist of arguments, In above syntax, the first expression is for text condition, if that condition will be true the it will return second vale else it will return third value.
lets take few examples to understand IIF function:
Example #1: IIF in Tableau with string or dimension
IIF([Item Type] = "Food" , "eatable Items" , "Other Items")
In above example IFF() function checks if the item type is Food (meaning if condition is true) then it will return new category as “eatable items” and if it is false then it will return “Other Items”
Example #2: IIF() in Tableau with Numbers or measures
An IIF statement also works with a numeric values both for aggregated and non-aggregated values.
Lets take example to check Total Sales for -ve and +ve values and show different level for positive and negative values
IIF([Total Sales] > 0 ,"Positive" , "Negative", "No Value")
Output will look like this
| Total Sales | IIF Output |
|---|---|
| 23,000 | Positive |
| Null | No Value |
| -1000 | Negative |
Example #3: Nested IIF() Function in Tableau
IIF statements can also be nested, however it will be more hard to read and understand if you will do more nested level, so recommended to have one nested IFF() function. if you need more condition then better to use IF statement.
Syntax for nested IFF()
IIF(<Condition_Test>, return value if condition true, IIF(<Condition_Test>, return value if condition true, return value if condition false))
lets take example
IIF([Item Type]="Food" , "eatable Items" , IIF([Item Type]="House" , "Home Items" , "Other Items"))
In above example IFF() function checks, if the item type is Food or House and accordingly it will return the values.