Forum Discussion

ErMley's avatar
ErMley
Frequent Visitor
2 years ago
Solved

Calculated Table for Non-Current and Current Records

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 - Positi...
  • ErMley's avatar
    ErMley
    2 years ago

    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]
        )


Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
    ErMley
    Frequent Visitor
    2 years ago

    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]
        )