Calculated Table for Non-Current and Current Records

[ErMley]

Hello, 

I am trying to provide some insights on employee movements within a company HR dataset. I have the following fields: 

1. GLOBAL_ID - Employee ID as a whole number 

2. JOB_TITLE - Position of employee as text 

3. CURRENT_RECORD - A binary text column with either 'N' or 'Y' to indicate whether a row is an employee's current record. An employee can have multiple rows based on employment history with different job titles, and the row with "Y" is the employee's up to date employment details. All "old" rows will have a "N" in this column. 

4. VALIDITY_START_DATE - a date column based on when that employee's record was updated with a new record. This is in a relationship with a date dimension table - DIM_DATE[Date]

I would like to visualise job title changes in the company in a chord diagram. To do this, I need a "From" field and a "To" field and it needs to be filterable by the aforementioned DIM_DATE[Date] slicer. 

I am thinking of approaching this problem by creating a Previous Position table with the employees' most recent job title and "N" in Current Record field, and a Current Position table with employees' job title and "Y" in Current Record field and using CROSSJOIN to join the tables by the GLOBAL_ID column but am running into many errors. Can someone assist me in building out some DAX to do this? 

[Anonymous]

Hi  @ErMley ,

I created some data:

Here are the steps you can follow：

1. Create calculated table.

TrueTbale =
VAR _table =
    FILTER ( 'Table', [CURRENT_RECORD] = "Y" )
VAR _table2 =
    ADDCOLUMNS (
        _table,
        "ToTest",
            MINX (
                FILTER (
                    _table,
                    [GLOBAL_ID ] = EARLIER ( [GLOBAL_ID ] )
                        && [VALIDITY_START_DATE] > EARLIER ( [VALIDITY_START_DATE] )
                ),
                [VALIDITY_START_DATE]
            )
    )
VAR _table3 =
    ADDCOLUMNS (
        _table2,
        "To",
            MINX (
                FILTER (
                    _table2,
                    [GLOBAL_ID ] = EARLIER ( [GLOBAL_ID ] )
                        && [VALIDITY_START_DATE] = EARLIER ( [ToTest] )
                ),
                [JOB_TITLE]
            )
    )
VAR _table4 =
    SELECTCOLUMNS (
        FILTER ( _table3, [To] <> BLANK () ),
        "GLOBAL_ID", [GLOBAL_ID ],
        "From", [JOB_TITLE],
        "To", [To]
    )
RETURN
    _table4

2. Result:

If the above results do not meet your expectations, can you express the expected results in the form of a picture, we can help you better.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[ErMley · Accepted solution]

The DAX code you provided was useful to break down into steps but no data was being pulled into my table once I executed it (even checking all column names, etc.).

I came up with the following to resolve the issue: 

JobChangesTable =

VAR CurrentRecords =

    SELECTCOLUMNS(

        FILTER(

            'Test Data',

            'Test Data'[Current Record ] = "Y"

        ),

        "GLOBAL_ID", 'Test Data'[GLOBAL_ID],

        "To Position", 'Test Data'[JOB_TITLE]

    )

VAR PreviousRecords =

    SELECTCOLUMNS(

        FILTER(

            'Test Data',

            'Test Data'[Current Record ] = "N"

        ),

        "GLOBAL_ID", 'Test Data'[GLOBAL_ID],

        "Validity Start Date", 'Test Data'[Validity Start Date],

        "From Position", 'Test Data'[JOB_TITLE]

    )

VAR RecentPreviousRecords =

    ADDCOLUMNS(

        GROUPBY(

            PreviousRecords,

            [GLOBAL_ID],

            "MaxDate", MAXX(CURRENTGROUP(), [Validity Start Date])

        ),

        "From Position",

        VAR GlobalID = [GLOBAL_ID]

        VAR MaxDate = [MaxDate]

        RETURN

            MAXX(

                FILTER(

                    PreviousRecords,

                    [GLOBAL_ID] = GlobalID &&

                    [Validity Start Date] = MaxDate

                ),

                [From Position]

            )

    )

VAR CrossJoinedTable =

    SELECTCOLUMNS(

        CROSSJOIN(

            SELECTCOLUMNS(CurrentRecords, "GLOBAL_ID_Curr", [GLOBAL_ID], "To Position", [To Position]),

            SELECTCOLUMNS(RecentPreviousRecords, "GLOBAL_ID_Prev", [GLOBAL_ID], "From Position", [From Position])

        ),

        "GLOBAL_ID_Curr", [GLOBAL_ID_Curr],

        "To Position", [To Position],

        "GLOBAL_ID_Prev", [GLOBAL_ID_Prev],

        "From Position", [From Position]

    )

RETURN

    FILTER(

        CrossJoinedTable,

        [GLOBAL_ID_Curr] = [GLOBAL_ID_Prev]

    )