Variable with multiple conditions

[dan-74]

Good afternoon.

I'm hoping that someone can help with a formula I am writing to return data from a second table

In this particular case I am trying to return details of the last job help by an employee in a separate field so that we can see what position the individual has moved from

=[Previous ocupancy].[Position]

This formula alone returns the results, but has #multivalues where employees have multiple lines depending on previous positions held

I have used the 'max' function to find the last role (labelled Most recent end date) and am joining detail on 'personal reference: People' - the common denominator.

=If(Max([End Date:Occupancy]) In ([Previous ocupancy].[Personal Reference:People]) = [End Date:Occupancy]) Then 1 Else 0

However, when adding another condition (ie:=[Previous ocupancy].[Position] Where( [Most recent End date] = 1) ,

I get an error message stating the expression uses an invalid data type

Using this formula I get blank results, despite there being data in the second report :

=If([Most recent End date]=1) Then [Previous ocupancy] Else ""

The first screen shot is the historic roles, the fifth column being the 'max' function results

The Second screenshot shows the resulting multivalue

Can someone please help understand where I am going wrong

Many thanks in advance

Dan