How does a LOOKUP Table Calculation work when the Offset argument is omitted?
A LOOKUP Function returns the value of the expression in a target row, specified as a relative offset from the current row.
Typically we would include an offset argument in a LOOKUP function.
LOOKUP Syntax: LOOKUP(expression, [offset])
But as observed the offset argument is specified within square brackets hence it is an optional argument.
But what would happen if we omit the offset argument?
To better understand this scenario let us create a simple view with the Sub-Category dimension on the Rows shelf and the Sales measure on Text of Marks card.
Next let us create a New Calculated Field for LOOKUP Sales as mentioned below. The expression inside the LOOKUP function in this case is SUM([Sales]) and we have not included the offset argument.
As per the description of the LOOKUP function (i.e. in the right side of the Calculation Editor Window) in case the offset is omitted, the Compare To row may be set on the Field Menu.
Bring the newly created calculated field LOOKUP Sales into the view.
The value returned by the LOOKUP function i.e. LOOKUP Sales without an offset is currently NULL for all the rows in the Table.
We can access the Field Menu by right-clicking the required field on the Measure Values shelf
We can right-click LOOKUP Sales in the Measure Values shelf, Select Relative to > Art (Art member is chosen here as an example)
Now we observe that the value returned by the LOOKUP function is the Sales value of Art for all the rows in the Table.
If required, we can change the Relative to option to any of the other members of the Sub-Category.
I hope this article is useful for all Tableau users especially for anyone just starting their Tableau journey!!!
