Create a new column from non related table

[kamiluc]

Hi,

I'm trying to create a new column in a table that will be based on another table which is not related.

The first table contains a user_ID which I want to lookup in the second table and bring the value for the employment status, but bring the value for the latest one.

Risks

Risk ID	Risk_Owner_User_ID	Risk Owner Employment Status (new column I need)
1	A	Active (currently getting "Inactive")
2	B	Active

Employee History

Employee ID	User_ID	Employee Status	Valid_To
123	A	Inactive	1/1/2020
456	A	Active	31/12/9999
789	B	Active	31/12/2023

My new column in risks table should include the Employee_Status for the latest Valid_To date.

I created this which is giving me some false-positives, and I've tried a few variations with no luck so far (the IF() is because some records don't have a user_ID and I was getting incorrect values in those):

Risk Owner Employment Status =

IF(

    Risks[Risk_Owner_UserID]<>"",

        CALCULATE(

            MAX(Employee_History[Employee_Status]),

            FILTER(Employee_History, MAX(Employee_History[ValidTo]) && Risks[Risk_Owner_UserID] = Employee_History[UserID]

            )

        ),

    ""

)

Thanks!

[Jihwan_Kim]

Hi,

Please check the attached pbix file and the below picture.

Risk Owner Employment Status =
IF (
    Risks[Risk_Owner_User_ID] <> "",
    MAXX (
        CALCULATETABLE (
            'Employee History',
            TREATAS (
                GROUPBY (
                    'Employee History',
                    'Employee History'[User_ID],
                    "@maxdate", MAXX ( CURRENTGROUP (), 'Employee History'[Valid_To] )
                ),
                'Employee History'[User_ID],
                'Employee History'[Valid_To]
            )
        ),
        'Employee History'[Employee Status]
    ),
    ""
)

[kamiluc · Accepted solution]

Thank you @Jihwan_Kim 

I reproduced the expression in my project but still had false-positives.  I don't fully understand what the TREATAS() does, but then I created a table with your CALCULATETABLE() to see what it was producing and it's the latest record for each User_ID.  What I believe it was missing was a link to the current User_ID in my risks table, so I included a CALCULATE() in the expression which appears to work now.

Risk Owner Employment Status =

IF (

    Risks[Risk_Owner_User_ID] <> "",

        CALCULATE(

            MAXX (

                CALCULATETABLE (

                    'Employee History',

                    TREATAS (

                        GROUPBY (

                            'Employee History',

                            'Employee History'[User_ID],

                            "@maxdate", MAXX ( CURRENTGROUP (), 'Employee History'[Valid_To] )

                        ),

                        'Employee History'[User_ID],

                        'Employee History'[Valid_To]

                    )

                ),

                'Employee History'[Employee Status]

            ),

        FILTER('Employee History', 'Employee History'[User_ID] = Risks[Risk_Owner_User_ID])

    ),

    ""

)