Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
wegemmell
Helper III
Helper III

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

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

 

1 ACCEPTED SOLUTION

HI @wegemmell,

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

1.pngLogic 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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

5 REPLIES 5
wegemmell
Helper III
Helper III

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
    )

 

HI @wegemmell,

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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!

HI @wegemmell,

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

1.pngLogic 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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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!

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.