# PowerPivot DAX Related Data Filtering

### Question

• Hello All,

I need help with the following (It must be easy, but I can't):

I have a table with employees and other with the positions they have had in the year(monthly).
I would like to add to employee's table a column with the last position of each.

The tables are related by employee ID

Thanks 4U help

________________________________________

Hola a todos,

Necesito ayuda con lo siguiente (debe ser fácil, pero no he podido)

Tengo una tabla con empleados y otra de las posiciones que han tenido en el año (mensualmente).
Quisiera a la tabla de empleados, agregarle una columna con la última posición de cada uno.

Gracias por la ayuda

Tuesday, August 27, 2013 11:52 AM

• Hello,

Sound a little bit like SCD = Slowly changing dimensions, right? Ok, lets say, your data model & ER looks like this: Table Employee + Position + historical positions per employee:

Instead of using one big DAX formula I split it in single steps to demonstrate the way to get the information.

1. First get the last date from "PositionHistory" for each employee: You can get it with the MAX function together with CALCULATE:

`=CALCULATE(Max(PositionHistory[Date]))`

2. Get the PositionId for the employee for the "last date": Lookup function

`=LOOKUPVALUE(PositionHistory[PositionId], PositionHistory[Date], Employee[LastPositionDate])`

3. Get the description for the position by the id: Again Lookup:

`=LOOKUPVALUE(Position[Postion], Position[PositionId], Employee[LastPositionId])`

That's it. And this is how it looks like in PoerPivot:

Olaf Helper

[ Blog] [ Xing] [ MVP]

Friday, August 30, 2013 3:03 PM

### All replies

• Hello,

Sound a little bit like SCD = Slowly changing dimensions, right? Ok, lets say, your data model & ER looks like this: Table Employee + Position + historical positions per employee:

Instead of using one big DAX formula I split it in single steps to demonstrate the way to get the information.

1. First get the last date from "PositionHistory" for each employee: You can get it with the MAX function together with CALCULATE:

`=CALCULATE(Max(PositionHistory[Date]))`

2. Get the PositionId for the employee for the "last date": Lookup function

`=LOOKUPVALUE(PositionHistory[PositionId], PositionHistory[Date], Employee[LastPositionDate])`

3. Get the description for the position by the id: Again Lookup:

`=LOOKUPVALUE(Position[Postion], Position[PositionId], Employee[LastPositionId])`

That's it. And this is how it looks like in PoerPivot:

Olaf Helper

[ Blog] [ Xing] [ MVP]

Friday, August 30, 2013 3:03 PM
• Thanks Olaf, very useful and  clear.

Regards

Wednesday, September 04, 2013 6:21 PM