Forum Discussion
Sorting out Compliance State
Hi DataNinja777 ,
thanks for your quick response. I see two problems:
1. The devices need to install both OS & Office Updates. We are talking about a server infrastructure where only 1% has installed Office. "LatestOSStatus" will never be blank. So the expression will always return the OS status only.
2. You say "Max Status" is inconsistent. I'm with you, because it's depending on the alphabet. Shouldn't we use "Max Date" instead?
Additional Information:
It could be possible that the table is looking like this:
| Assignment | Device | Status | StatusTime |
| UpdateAssignment OS1(Server2025) | Device1 | Compliant | 18.02.25 01:00 |
| UpdateAssignment OS2(Server2016) | Device1 | Waiting for Maintenance Window | 18.02.25 01:01 |
| UpdateAssignment OS3(Server2019) | Device1 | Waiting for Maintenance Window | 18.02.25 01:03 |
| UpdateAssignment Office1(Office2016) | Device1 | Compliant | 18.02.25 03:00 |
| UpdateAssignment Office2(Office365) | Device1 | Waiting for Maintenance Window | 18.02.25 03:01 |
Do you know what I mean? 🙂
Best regards
Robert
Hi DonRoberto ,
Since devices need to install both OS and Office updates, and we should determine the latest status based on StatusTime rather than Status, the best approach is to find the most recent StatusTime for each device and return the corresponding status. Using MAX(StatusTime), we identify the latest update time for each device, and then TOPN(1) ensures we select only the most recent status entry. This method ensures that neither OS nor Office updates are prioritized arbitrarily and that the device's latest update determines the compliance status.
Latest Update Status =
VAR LatestTime = CALCULATE(
MAX('Table'[StatusTime]),
ALLEXCEPT('Table', 'Table'[Device])
)
RETURN CALCULATE(
SELECTCOLUMNS(
TOPN(1, 'Table', 'Table'[StatusTime], DESC),
"LatestStatus", 'Table'[Status]
),
ALLEXCEPT('Table', 'Table'[Device])
)
This formula ensures that the most recent update—whether OS or Office—is always considered, preventing scenarios where an older OS update is mistakenly prioritized over a newer Office update. If the latest update for a device is an Office update, it will correctly return that status, and vice versa. Let me know if this works better for your use case!
Best regards,
Ok, but then I get only the latest status + status time which I can also make visible in my dashbord without calculating it. I think what I need is, is a custom column which shows per device the state(s) =! compliant. If all assignments are compliant, then all is compliant. If one is not compliant, then all is not compliant and gets the state of the non-compkliant one (e.g. waiting for maintenece window, installing, reboot required).
BR & THX
Robert