Forum Discussion
Sorting out Compliance State
Hi DonRoberto ,
Your issue arises because devices are reporting compliance based on Office updates rather than OS updates, leading to incorrect status filtering. Since some devices do not evaluate OS assignments they do not belong to, relying on "Max Status" is inconsistent. The solution is to ensure that OS updates take precedence over Office updates when determining compliance.
First, create a calculated column to classify updates as either OS-related or Office-related. You can achieve this with the following DAX expression:
UpdateType =
IF( CONTAINSSTRING('Table'[Assignment], "OS"), "OS", "Office")
Once the updates are classified, define a measure that retrieves the latest OS update status if available. If no OS updates exist for a device, the measure should fall back to the latest overall update status. This can be accomplished using:
Latest OS Status =
VAR LatestOSStatus =
CALCULATE(
MAX('Table'[Status]),
'Table'[UpdateType] = "OS"
)
VAR LatestOverallStatus =
MAX('Table'[Status])
RETURN
IF(NOT(ISBLANK(LatestOSStatus)), LatestOSStatus, LatestOverallStatus)
By applying this measure in your Power BI report, you ensure that devices are evaluated based on OS update status first. If a device has OS updates, the measure will return the most recent OS status. If a device only has Office updates, it will return the latest Office update status. This approach prevents Office updates from incorrectly marking devices as compliant before OS updates are processed.
Best regards,
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