# 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

• Moved by Friday, August 30, 2013 2:17 AM
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]

• Proposed as answer by Monday, September 2, 2013 12:50 PM
• Marked as answer by Wednesday, September 4, 2013 1:45 AM
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]

• Proposed as answer by Monday, September 2, 2013 12:50 PM
• Marked as answer by Wednesday, September 4, 2013 1:45 AM
Friday, August 30, 2013 3:03 PM
• Thanks Olaf, very useful and  clear.

Regards

Wednesday, September 4, 2013 6:21 PM