Staff Log Tracker

[hassanh2]

Hello,

Im trying to design a visual that can highlight the following scenarios.

the data set is for a clinic where they perform surgical procedures, nurses are required to Sigin/out from each procedure befor signing in to another one, but in some cases the nurses dont sign out from previous surgery and sign in into a new one,,, so as per the records she will appear present in 2 locations (rooms) at the same time. 

if any one can help with suggesting a visual that can highlight such cases ... so basically it should list all the log entries and highlight the lines / rows where the log time is in between the previous log(s). 
I attached a sample belwo to visualise the required output

[Dinesh_Y]

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 you

[HarishKM]

@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

[hassanh2]

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"

[HarishKM]

@hassanh2 you can create a calculated column and use this logic

[hassanh2]

What will be the sytax for that? If you are referring to this one SELECTEDVALUE(TableName[ColumnName]) - I tried it but it didt work

[HarishKM]

@hassanh2 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
)