find computers without a software installed

[oprzybylski]

I have a simple data set with several rows. Each row has a computer name and the name of a software installed on it, like so:

computer1	software1
computer1	software2
computer1	software3
computer2	software1
computer2	software2
computer2	software4

I've already found a way to count how many computers are missing, say "software3" but how do I create a table that contains those computers or visualize those computers in some way?

[Anonymous · Accepted solution]

Hi @oprzybylski ,

Here's my solution. Also upload the attachment for your reference.

1.Create a calculated table using DAX.

Software = DISTINCT('Table'[Software name])

2.Create a slicer using the field in the calculated table.

3.Create a measure for the visual-level filters. Put the measure into the filters and set up show items when the value is 1.

Measure = 
VAR _sel =
    SELECTEDVALUE ( 'Software'[Software name] )
RETURN
    IF (
        CALCULATE (
            MAX ( 'Table'[Computer Name] ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                [Software name] = _sel
                    && [Computer Name] = MAX ( 'Table'[Computer Name] )
            )
        )
            = BLANK (),
        1
    )

4.Here's the result. If you want to keep only the computer names, it also works.

Best Regards,

Stephen Tao

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

[Adamboer]

To create a table that contains the computers missing a specific software or visualize them in some way, you can follow these steps in Power BI:

1. Create a new table that lists all computers in the dataset. You can do this by selecting the "Modeling" tab and then clicking on "New Table" and entering the formula:

   Computers = DISTINCT('Table'[computer name])

   This will create a new table that lists all the unique computer names in the dataset.

2. Create a measure that counts the number of computers missing a specific software. You can do this by selecting the "Modeling" tab and then clicking on "New Measure" and entering the formula:

   MissingSoftwareCount = CALCULATE(DISTINCTCOUNT('Table'[computer name]), NOT('Table'[software name] = "software3"))

   This will create a measure that counts the number of computers that do not have the "software3" installed.

3. Create a new table or visualization that lists the computers missing the software. You can do this by selecting the "Visualizations" tab and then clicking on "Table" or any other desired visual. Then, add the "Computers" column to the Rows field and the "MissingSoftwareCount" measure to the Values field. Finally, add a filter to the visual that excludes any computers that have the "software3" installed.

   This will create a table or visual that lists all the computers missing the "software3".

[oprzybylski]

@Adamboer thank you for the reply. I understand the concepts but it does not seem to work for my file.

- step 1 is OK, it create a new table with *ALL* the computers (computer1 and computer2)

- step 2 does not return the right value. That formula seems to still count one of the lines of computer2 as NOT missing software3, so ultimately the formula returns the count of ALL the computers. I was able to modify it by doing this formular instead (basically the diffference between the count of all the computers minus the ones that do have software3): MissingSoftwareCount = CALCULATE(DISTINCTCOUNT('Table'[computer name]) - DISTINCTCOUNT('Table'[computer name]),'Table'[software name] = "software3")
- step 3 is where I'm really confused. I don't see how to connect that count of computers missing software3 to the actual list.

Could you elaborate a bit more on that?

[ThxAlot]

You can't use PQ in an interactive manner

Software.pbix