locked
PowerPivot DAX Related Data Filtering RRS feed

  • 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

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

    • Proposed as answer by Elvis Long Monday, September 2, 2013 12:50 PM
    • Marked as answer by Elvis Long 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 Elvis Long Monday, September 2, 2013 12:50 PM
    • Marked as answer by Elvis Long 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