none
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.

    Las tablas están relacionadas por la identificación del empleado.

    Gracias por la ayuda

    Tuesday, August 27, 2013 11:52 AM

Answers

  • 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
    Answerer

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
    Answerer
  • Thanks Olaf, very useful and  clear.

    Regards

    Wednesday, September 04, 2013 6:21 PM