LET Function

LET allows you to reuse logic in complex expressions. You can declare expression-level variables using the DIM statement, initialize them, and reuse them.

Syntax:

LET(variable_declarations, expression)

The following example creates two variables, [AvgUnitPrice] and [TotalQuantity]. Each is initialized with a LOOKUPVALUE expression. The variables are then used in a resulting expression. The lookup expressions only need to be declared once, instead of repeated multiple times in the IIF block.

LET(
DIM [AvgUnitPrice] AS FLOAT = LOOKUPVALUE(0,[Products.CategoryName],1,SELECT [Products.CategoryName],[OrderDate: Avg: UnitPrice] FROM [All])
DIM [TotalQuantity] AS FLOAT = LOOKUPVALUE(0,[Products.CategoryName],1,SELECT [Products.CategoryName],[OrderDate: Sum: Quantity] FROM [All]),
IIF([AvgUnitPrice]<25,25,[AvgUnitPrice])*[TotalQuantity])