LOOKUP

A LOOKUP expression is a simple form of join. It treats the result of a prior statement as a lookup table.

Its syntax is as follows:
<statement>[<expression list>].<attribute>

The expression list corresponds to the grouping attributes of the specified statement. The result is NULL if the expression list does not match target group key values, or the target column is NULL for a matching target group key values.

Lookup attributes refer to GROUP BY clauses of the target statement, in order. Computed lookup of indexed values is allowed, which means you can look up related information, such as total sales from the prior year, as shown in the following example:
DEFINE YearTotals AS SELECT
  SUM(SalesAmount) AS Total
GROUP BY Year ;

RETURN AnnualCategoryPcts AS SELECT
  SUM(SalesAmount) AS Total,
  Total/YearTotals[Year].Total AS Pct
GROUP BY Year, Category ;

RETURN YoY AS SELECT
  YearTotals[Year].Total AS Total,
  YearTotals[Year-1].Total AS Prior,
  (Total-Prior)/Prior AS PctChange
GROUP BY Year