locked
Value of text-field dependent on time-filter RRS feed

  • Question

  • In our model there are two tables: a task table and a table that contains time periods that an employee is working for a specific department. An employee is assigned to a task and with the information from the period table the task belongs to a department.

    Table task:           taskname

                               Creationdate

                               Employeename

                               departmentname (calculated field)

    Table period:        employeename

                               departmentname

                                startdate

                                enddate (31-12-9999 [d-m-y] if not set)

    There is also a dimdate time table

    The departmentname field in the task table is calculated on employeename and task-creationdate between startdate and enddate in the period table.

    The user can make reports filtered on department reflecting the number of assigned tasks.

    There is a problem when a user is assigned to another department. The employee will get a new line in the period table and the enddate of the old line is set. But the task will stay assigned to the old department.

    Ex. Task creationdate 1-1-2016 (d-m-y) -> the employee to whom the task is assigned changes to another department at 5-1-2016.

    Our user wants to be able to create a report filtered on date and if he selects 4-1-2016 the task belongs to the old department and on 6-1-2016 to the new department. I don’t think this can be done with a calculated field.

    I tried a couple of things for ex to build a measure like

    FIRSTNONBLANK(Period[departmentname]; Period[departmentname]<>BLANK()&&MAX(Period[Startdate])<MAX(DimDate[date])&&MAX(Period[Enddate])>=MAX(DimDate[date]))

    Dimdate[date] is the filtered date that the user selects. But this just gives me the first departmentname. Also the employeename has to be in the expression. If it was a number I could try something with MAX and MIN, but it is text so I can’t use those formula’s.

    What would be the best practice to do this?


    • Edited by Wim Wensel Tuesday, February 16, 2016 3:29 PM
    Tuesday, February 16, 2016 3:27 PM

Answers

  • This sounds like a dynamic segmentation problem and you are correct that it can't be solved by a basic calculated column.

    Basically your Period table is a slowly changing dimension (SCD).

    The exact formula you need to solve it might vary a bit depending on the final layout of your pivot report and slicers but the basic idea is to create a pattern where your filter condition checks the date range (and in your case the employee).  Something along these lines is what you are looking to write:

    DynamicDept :=
    IF (
        HASONEVALUE ( Task[CreationDate] ),
        CALCULATE (
            VALUES ( Period[Department] ),
            FILTER (
                Period,
                VALUES ( Task[CreationDate] ) >= Period[StartDate]
                    && VALUES ( Task[CreationDate] ) < Period[EndDate]
                    && VALUES ( Task[EmployeeName] ) = Period[EmployeeName]
            )
        ),
        BLANK ()
    )
    

    Again, there are a lot of variations on how you write this measure and possibly some dependencies on the layout of you final report.  Instead of returning the actual department name, you might just return some numeric value like a sum or row count.

    See this link for details on Dynamic Segmentation as well as many other useful patterns:

    http://www.daxpatterns.com/dynamic-segmentation/

    Thursday, February 18, 2016 10:08 PM
    Answerer
  • Implemented the solution proposed by Mike and it works. Thanks very much.

    • Marked as answer by Wim Wensel Tuesday, February 23, 2016 1:46 PM
    Tuesday, February 23, 2016 1:45 PM

All replies

  • This sounds like a dynamic segmentation problem and you are correct that it can't be solved by a basic calculated column.

    Basically your Period table is a slowly changing dimension (SCD).

    The exact formula you need to solve it might vary a bit depending on the final layout of your pivot report and slicers but the basic idea is to create a pattern where your filter condition checks the date range (and in your case the employee).  Something along these lines is what you are looking to write:

    DynamicDept :=
    IF (
        HASONEVALUE ( Task[CreationDate] ),
        CALCULATE (
            VALUES ( Period[Department] ),
            FILTER (
                Period,
                VALUES ( Task[CreationDate] ) >= Period[StartDate]
                    && VALUES ( Task[CreationDate] ) < Period[EndDate]
                    && VALUES ( Task[EmployeeName] ) = Period[EmployeeName]
            )
        ),
        BLANK ()
    )
    

    Again, there are a lot of variations on how you write this measure and possibly some dependencies on the layout of you final report.  Instead of returning the actual department name, you might just return some numeric value like a sum or row count.

    See this link for details on Dynamic Segmentation as well as many other useful patterns:

    http://www.daxpatterns.com/dynamic-segmentation/

    Thursday, February 18, 2016 10:08 PM
    Answerer
  • Implemented the solution proposed by Mike and it works. Thanks very much.

    • Marked as answer by Wim Wensel Tuesday, February 23, 2016 1:46 PM
    Tuesday, February 23, 2016 1:45 PM
  • Wim,  glad my solution worked for you.  Please mark my original reply as an answer when you have a chance since it helped you solved your problem.  Thanks!
    Tuesday, February 23, 2016 2:08 PM
    Answerer