Sorting out Compliance State

[DonRoberto]

Hi,

I'm using MECM for patchmanagement and use a powerbi report for monitoring. The table I'm talking about is the following:

Assignment	Device	Status	StatusTime
UpdateAssignment OS1	Device1	Waiting for Maintenance Window	18.02.25 01:00
UpdateAssignment OS2	Device1	Waiting for Maintenance Window	18.02.25 01:01
UpdateAssignment OS3	Device1	Waiting for Maintenance Window	18.02.25 01:03
UpdateAssignment Office1	Device1	Compliant	18.02.25 03:00
UpdateAssignment Office2	Device1	Compliant	18.02.25 03:01

In the last month I switched the filter from "Max Status"(A-Z) to "Max Date" by using a helping table. This month all devices(where no office is installed) are reporting as compliant, because they evaluated the office updates as latest. Beginning with their maintenance window the switch the state to e.g. "Installing Updates".

Max Status doesn't work from time to time, because some devices doen't evaluate the the OS assignment which they not belong to.

Does anyone have a idea how I can filter this out?

THX

Robert

[DataNinja777]

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,

[DonRoberto]

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

[DataNinja777]

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,

[DonRoberto]

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