Looking for help with a complex DAX column tied to a standard relationship and change audits

[Anonymous]

Good day, Community!

Hoping someone might be able to assist with a calculated DAX column I am struggling with. I will try to do my best to explain...

Table references as in the attached example data

The column would start by pulling in a User's Department from a standard related table (User Table [USERKEY]) into a date related time fact table [Time_Data_Table] if until the user's department has been changed, which would be recorded in the [Department_Change_History] table. If changed, I would need to pull the old department [From Department] value for all of the historic time entries up until the date/time the change was made. This would be for each change that has taken place, as some users may have changed more than once.

Example

USERKEY 742's Department was changed from PBIHA to PBIM on 8/2/2023 22:37. In the time data table, I would need USERKEY 742's new "USER DEPARTMENT" column to read as PBIHA for any time entry prior to the change date/time. Any user without a change would simply be the department listed in the User_Table in this same column.

Again, if a user had no changes associated in the audit table, the new User Department would simply be what is recorded in the User_Table.

Any thoughts and or help here is immensely appreciated!!

Example data:

Example_Data.xlsx

[Anonymous]

So, it's the source data I am struggling with. I need to be able to get the From Department value for each time entry where the time entry date is before the date changed in the department change history. The issue I'm running into is when there are multiple changes per user in the change history...

VAR Hist_Dept = 
    CALCULATE(
        SELECTEDVALUE(Department_Change_History[From Department]),
            FILTER(
                ALL(Department_Change_History),
                Department_Change_History[USERKEY] = Time_Data_Table[PERFORMEDBYKEY] &&
                Department_Change_History[Date Changed] >= Time_Data_Table[Actual Start]
            )
    )

RETURN
    IF(
        ISBLANK(Hist_Dept),
        RELATED(User_Table[User Department]),
        Hist_Dept
    )

[v-shex-msft]

HI @Anonymous,

Perhaps you can try to use the following expression to create a calculated column on 'Time_Data_Table' if it helps:

USER DEPARTMENT =
VAR Hist_Dept =
    CALCULATE (
        MAX ( Department_Change_History[From Department] ),
        FILTER (
            Department_Change_History,
            [USERKEY] = Time_Data_Table[PERFORMEDBYKEY]
                && AND (
                    [Date Changed] >= Time_Data_Table[Actual Start],
                    [Date Changed] <= Time_Data_Table[Actual End]
                )
        )
    )
RETURN
    IF (
        Hist_Dept = BLANK (),
        LOOKUPVALUE (
            User_Table[Department],
            User_Table[USERKEY], Time_Data_Table[PERFORMEDBYKEY]
        ),
        Hist_Dept
    )

Regards,

Xiaoxin Sheng

[Anonymous]

Hi, @v-shex-msft 

I tried a variation of that and what you provided, but that didn't seem to do the trick either.

I have created a new sample set of data here. The "Dept_Hist" tab contains the changes of a single user. The "Labor_Data" tab are all of the user's time punches. Column G ("Hist Department") shows the expected results. The other piece of the puzzle where a user has no change history is easy. It's just getting the from department based on the time stamps inbetween each change where multiple changes exist is the wall I keep running into. 

Your help and insight here is greatly appreciated!

[v-shex-msft · Accepted solution]

HI @Anonymous,

You can try to use the following calculated column formula(include the comment) to get the history dept based on condition:

HistDeptByCondition =
VAR _start =
    MINX ( Dept_Hist, [Date Changed] )
VAR _end =
    MAXX ( Dept_Hist, [Date Changed] )
RETURN
    IF (
        //records that out of the history table ranges
        Labor_Data[Actual Start] > _end,
        Labor_Data[User Department],
        IF (
            Labor_Data[Actual End] < _start,
            LOOKUPVALUE (
                Dept_Hist[From Department],
                Dept_Hist[USERKEY], Labor_Data[PERFORMEDBYKEY],
                Dept_Hist[Date Changed], _start
            ),
            //start date before or equal to change date and end date equal to department change date
            VAR endDateMatch =
                MAXX (
                    FILTER (
                        Dept_Hist,
                        Dept_Hist[Date Changed] >= Labor_Data[Actual Start]
                            && DATEVALUE ( Labor_Data[Actual End] ) = DATEVALUE ( Dept_Hist[Date Changed] )
                    ),
                    [From Department]
                )
            RETURN
                IF (
                    endDateMatch <> BLANK (),
                    endDateMatch,
                    //if not end at the same day, find the next change records and use its 'from department' as history
                    MINX (
                        FILTER ( Dept_Hist, Dept_Hist[Date Changed] >= Labor_Data[Actual End] ),
                        [From Department]
                    )
                )
        )
    )

Logic of dax formula conditions:

1. Records that out of history table ranges.
2. End date equal to the change date. 
3. Records between two history records.

BTW, I also attach the sample pbix file that used the second excel file as source, you can try it if helps.

Regards,
Xiaoxin Sheng

[Anonymous]

You're awesome! That looks like it works perfectly in the test model. The only issue I'm running into now is the complexity of the calculation. My actual model has a million plus rows and growing, so it seems to be too taxing of a formula (runs out of memory trying to calculate). I will keep churning to see if there is any way of possibly making it more efficient, but it may just be too much to handle either way. 

Thank you again!