Auditing machines using power bi

[bhmiller89]

I have an Excel spreadsheet that lists ComputerName and Application. The ComputerName column has repeating values as each machine has multiple applications installed.

If all of the required applications are installed on a machine, the machine needs to be marked "Compliant." if any of the applications are missing it needs to be marked "Non-Compliant" 

If a machine is "Non-Compliant" I then need to be able to visualize those ComputerNames and which Applications are missing. 

Any ideas how to do this is appreciated 

[Mariusz]

Hi @bhmiller89 

Sure, can you provide a data sample?

Best Regards,
Mariusz

Please feel free to connect with me.

[bhmiller89]

@Mariusz Here's some basic dummy data. If the 5 required applications to make a machine compliant are Word, Excel, Spotify, ShoreTel, and PowerBI, only Mach1 is compliant in the dummy data. Thanks for your help!

ComputerName         Application

Mach1	Word
Mach1	Excel
Mach1	Spotify
Mach1	ShoreTel
Mach1	PowerBI
Mach2	Word
Mach2	Excel
Mach2	Spotify
Mach3	Word
Mach3	Excel
Mach3	Spotify
Mach3	PowerBI

[Mariusz]

Hi @bhmiller89 

Try this.

Measure = 
VAR _compliantList = { "Word", "Excel", "Spotify", "ShoreTel", "PowerBI" }
RETURN
    SUMX(
        VALUES( 'Table'[ComputerName] ),
        INT(
            CALCULATE( 
                COUNTA( 'Table'[Application] ), 
                TREATAS( _compliantList, 'Table'[Application] ) 
            ) = COUNTROWS( _compliantList )
        )
    )

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[bhmiller89]

@Mariusz  that worked great to get the values! 

Can you assist in helping me list out the Non-Compliant machines and their missing applications? 

[Mariusz · Accepted solution]

Hi @bhmiller89 

Please see the attached file.

I did have to add an extra table "Applications" and create a relationship with the sample table that you provided, this allows to list applications that are missing.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.