Forum Discussion

Cepheus's avatar
Cepheus
Frequent Visitor
2 years ago

Windows Server Patch Report

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.

General Comment

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    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-solution-8e3eccb41bda

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.