BI Tips: LookUp Functions

LookUp functions can be used to cross reference information across multiple datasets. These expressions cannot be used in the query designer, only in a tablix expression.

For example, let’s say you have two datasets:

  • Your tablix uses Dataset1 to gather its data, but you’d like to add Product Cost as a column. Since Product Cost lives in another dataset, you would have to use a lookup function to accomplish this.
  • We will call the dataset being used by your report Home” and the other dataset Away.”
  • If you don’t know which dataset your table is using, right-click on your table > Tablix Properties
  • In order to use a lookup function, you must have a common field (or key) on which to join the information.
  • In the example above, Product SKU is present in both datasets and can be used as our key.”

Lookup expressions are written with the following syntax:

=LookUp( HomeKey, AwayKey, AwayValue, AwayDatasetName”)

My expression would be:

=LookUp( Fields!ProductSKU.value, Fields!ProductSKU.value, Fields!ProductCost.value, Dataset2”)

For more information on this topic: