Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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:
Solved! Go to 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
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 @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
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 @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
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!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
31 | |
15 | |
11 | |
10 | |
8 |