Forum Discussion
Staff Log Tracker
Hi hassanh2 ,
Thank you for reaching out to the Microsoft Community Forum.
Please follow below steps:
1. Table Visual with Conditional Formatting
Use a table to display all log entries with columns: Staff Name, OR Room, IN OR, OUT OR and Conflict Flag (new field)And use conditional formatting to highlight rows where there is a time conflict.
Create a calculated column or measure to flag the overlap.
ConflictFlag =
VAR CurrentStaff = 'Logs'[Staff Name]
VAR CurrentIn = 'Logs'[IN OR]
VAR CurrentOut = 'Logs'[OUT OR]
VAR Overlaps =
CALCULATE(
COUNTROWS('Logs'),
FILTER(
'Logs',
'Logs'[Staff Name] = CurrentStaff &&
'Logs'[IN OR] < CurrentOut &&
'Logs'[OUT OR] > CurrentIn &&
'Logs'[Log ID] <> EARLIER('Logs'[Log ID])
)
)
RETURN IF(Overlaps > 0, "Conflict", "OK")Use conditional formatting in the table visual to color rows: Red: if ConflictFlag = "Conflict" and
Green: if ConflictFlag = "OK"You can even add arrows or icons using Unicode symbols or Power BI icons to make it more visually intuitive (like the ones in your image).
Note:
Other Visuals: Timeline Chart / Gantt: If you want to visualize a nurse’s presence in the ORs over time, a Gantt chart (with staff on the Y-axis and time on the X-axis) can show overlaps clearly.
Matrix View: Staff vs. OR Room vs. Time Blocks. Highlight time overlaps.
If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.
Thank youhassanh2 Hey,
Copy paste this same measure only but as calculated column.MissingSignOutFlag =
VAR CurrentNurse = [NurseID] -- Assumed Nurse ID or Name column
VAR CurrentStartTime = [SignInTime] -- Assumed column for Sign-In time
VAR PreviousSignOutTime =
CALCULATE(
MAX('ProcedureLog'[SignOutTime]),
FILTER(
'ProcedureLog',
'ProcedureLog'[NurseID] = CurrentNurse &&
'ProcedureLog'[SignOutTime] < CurrentStartTime
)
)
RETURN
IF(
ISBLANK(PreviousSignOutTime),
1, -- Flag as missing sign out (overlap)
0 -- No overlap
)
hassanh2 Hey,
Could you please create a measure that will help to identify sign out completed or not?
MissingSignOutFlag =
VAR CurrentNurse = [NurseID] -- Assumed Nurse ID or Name column
VAR CurrentStartTime = [SignInTime] -- Assumed column for Sign-In time
VAR PreviousSignOutTime =
CALCULATE(
MAX('ProcedureLog'[SignOutTime]),
FILTER(
'ProcedureLog',
'ProcedureLog'[NurseID] = CurrentNurse &&
'ProcedureLog'[SignOutTime] < CurrentStartTime
)
)
RETURN
IF(
ISBLANK(PreviousSignOutTime),
1, -- Flag as missing sign out (overlap)
0 -- No overlap
)
then I will create table visual and drag required column then I will add a conditional formating like if measure =1 then red else green
steps to create CF
Apply Conditional Formatting to the Table:
Highlight the rows where the MissingSignOutFlag is 1.
You can color these rows with a red background, or a specific text color to immediately highlight nurses who have overlapping procedures.
Note : . Line Chart or Gantt Chart Visualization or Scatter plot will suitable to find overlap or outlier in your data.
Thanks
Harish M
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query
Thank you Harish!
The logic make sense, but unfrotuantely its not accepting assiging column value (i.e Nurse ID) as a Variable.
I tried to use SELECTEDVALUE(TableName[ColumnName]) as a variable, it measure syntax was correct but it did not return the correct flag.... all records were marked as "Overlap"
