Formula to work in the Regular Column not a Virtual Column

I want to get the Current Salary of an employee using the following formula:

SELECT ( EmpSalHist [Salary], [EmpSalID] = MAXROW ( “EmpSalHist”, “SalChangeDate”, AND ( ( [EmpID] = [_THISROW].[EmpName] ), ( [SalChangeDate] < [SalDate] ) ) ) )

If I put this in the Column Type Number it gives error:
Column Name ‘CurrSal’ in Schema ‘EmpSal_Schema’ of Column Type ‘Number’ has an invalid app formula ‘=SELECT ( EmpSalHist [Salary], [EmpSalID] = MAXROW ( “EmpSalHist”, “SalChangeDate”, AND ( ( [EmpID] = [_THISROW].[EmpName] ), ( [SalChangeDate] < [SalDate] ) ) ) )’. The type of the app formula ‘List of Price’ does not match the column type ‘Number’.

BUT if I put the same in Virtual Column of List Type it works!!
It fetches the Current Salary and shows.

What do I do for this formula to work in the Regular Column not a Virtual Column.

Please Help! Thank you!! :blush::pray:

Select expression is always to return the list type.
You need to wrap whole expression by ANY() or index() expression to match the column type of number.

2 Likes

Absolute Genius!! Thank you so much!! God Bless!! :pray::blush:

As per your advice I put the following formula:

ANY ( SELECT ( EmpSalHist [Salary], [EmpSalID] = MAXROW ( “EmpSalHist”, “SalChangeDate”, AND ( ( [EmpID] = [_THISROW].[EmpName] ), ( [SalChangeDate] < [SalDate] ) ) ) ) )

Works like a charm!! :blush:

2 Likes