Windows Server Patch Report

[Cepheus]

Hello Everyone, I have an Excel file with things like comptuer name,  patch id, deployment date, installed status, deployment date etc. I am new to  PowerBI and started using it about a year ago but stopped using it at about the same time when things got busy, more like I forgot about it.

This Excel report has multiple rows with the same computer name for patch that has tried to be installed. Installed status column constains values such as failed, installed, deplayed deployment and pending reboot. I would like to come up with a summarization that shows total patches installed (for a given date range if possible), which patches are missing from which computers, the various statuses etc.

Any assitance is appreaciated.

[Anonymous]

Hi @Cepheus 

I ran the following test:

My sample:

1. Create a calculated table as follows

Date = VALUES('Table'[deployment date])

2. Create several measures as follow

Sum = 
CALCULATE (
    COUNT ( 'Table'[installed status] ),
    FILTER (
        'Table',
        'Table'[installed status] <> "failed"
            && 'Table'[deployment date] >= MIN ( 'Date'[deployment date] )
            && 'Table'[deployment date] <= MAX ( 'Date'[deployment date] )
    )
)

missingPatch = 
CALCULATE (
    SUM ( 'Table'[patch id] ),
    FILTER (
        'Table',
        'Table'[installed status] = "failed"
            && 'Table'[deployment date] >= MIN ( 'Date'[deployment date] )
            && 'Table'[deployment date] <= MAX ( 'Date'[deployment date] )
    )
)

Is this the result you expect?

If I've misunderstood you, please provide detailed sample data and the results you are hoping for: How to provide sample data in the Power BI Forum - Microsoft Fabric Community . Please remove any sensitive data in advance.

Best Regards,
Community Support Team _Yuliax

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Cepheus , A measure below should help to respond to the slicers from table and related tables
Total Patches Installed =
CALCULATE(
COUNTROWS('Table'),
filter('Table','Table'[Installed Status] = "installed")
)


Better to create a star schema
Power BI- Power Query: When I asked you to create common tables: https://youtu.be/PqfGW6pl1Sw
Power BI- DAX: When I asked you to create common tables: https://youtu.be/a2CrqCA9geM
https://medium.com/@amitchandak/power-bi-when-i-asked-you-to-create-common-tables-a-quick-dax-soluti...