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!!

Solved Solved
0 2 131
1 ACCEPTED SOLUTION

Absolute Genius!! Thank you so much!! God Bless!!

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!!

View solution in original post

2 REPLIES 2

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.

Absolute Genius!! Thank you so much!! God Bless!!

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!!

Top Labels in this Space