Sorting out Compliance State

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,