Dereference vs lookup()

What is the difference between dereference and lookup()? Both seems to find the references row to pull column value from another table.

Thank you!

In general you are correct. As per my understanding ,

Both dereference and LOOKUP() can produce an identical end result.

However

  1. Dereference expression needs the two tables to be related by referencing relationship. One can then get ( lookup) values from parent table in the child table through derference expression

  2. Lookup does not need the two tables in reference relationship. However there needs to be at least one matching column / value between two tables to get a value from the other table.

  3. As per my understanding, if the reference relationship is established between two table, it is better to use the dereference expressions in the child table if one wants to get multiple values from the parent table. LOOKUP() will perform a multrow operation each time.

Example : “Order Details” table ( Child table) references the “Orders” table through “Orders” table’s key column [Order ID].
One could get [Order Date] from the "Orders Table in the “Order Details” table by any of the below expressions
A) By dereferencing
[Order ID].[Order Date]

B) By LOOKUP()
LOOKUP([_THISROW].[Order ID], “Orders”, “Order ID”, “Order Date”)

However since the two tables are in referencing relationship , I believe dereference expressions are more efficient way.

The below help articles explain it very well.

4 Likes