Forum Discussion

oprzybylski's avatar
oprzybylski
New Member
3 years ago
Solved

find computers without a software installed

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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's avatar
Adamboer
Responsive Resident
3 years ago

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's avatar
    oprzybylski
    New Member
    3 years ago

    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?