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