LET Function

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

Syntax:

LET(variable_declarations, expression)

This 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])