Check for string in duplicate rows

[lynnsop]

Hello, I need some help. Here is what I am trying to aim for:

Lets say I have this table:

Server1	Compliant
Server1	Compliant
Server1	Compliant
Server2	Compliant
Server2	Non-Compliant
Server2	Compliant

Now, my goal is that a Piechart shows a (in this example) 50/50 Compliance due to Server2 failing one compliance check. That means that once a Server fails a single Compliance check, the entire server should be reported in the Piechart as Non-Compliant giving a summary of how many servers are Non-Compliant and how many are compliant.

Thats basically already it but I cant figure out a way/logic that takes the entire server, check for Column2 if its compliant in every "check"/row of this server and give me an overall Compliance result for this server as a basis to use to display it on a Piechart.

I didnt really know how to give that a suitable subject, so I hope could explain it understandbly and short. Looking forward for solutions/advices! 🙂

[jdbuchanan71]

You can add it as a calculated column but the DAX is different for that.

Overall Status = 
VAR _Server = 'Table'[Server]
VAR _NonCount = 
CALCULATE(
    COUNTROWS('Table'),
    ALLEXCEPT('Table','Table'[Server]),
    'Table'[Server]=_Server,
    'Table'[Status] = "Non-Compliant"
)
RETURN
IF ( ISBLANK ( _NonCount ), "Compliant", "Non-Compliant")

I have attached my sample file for you to look at.  Plase note the visual is using the [Server Count] measure.

[jdbuchanan71 · Accepted solution]

@lynnsop 

Give these a try.

Non-Compliant = 
CALCULATE(
    DISTINCTCOUNT('Table'[Server]),
    'Table'[Result] = "Non-Compliant"
)

Compliant = 
VAR _Non =
    CALCULATETABLE ( VALUES ( 'Table'[Server] ), 'Table'[Result] = "Non-Compliant" )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Server] ),
        EXCEPT ( VALUES ( 'Table'[Server] ), _Non )
    )

You should also be able to do the Compliant like this.

Compliant = 
DISTINCTCOUNT('Table'[Server]) - [Non-Compliant]

Just subtracting the non-compliant count from the total count.

[lynnsop]

@jdbuchanan71 

Thank you for your answer!

Unfortunately its not quiet working.

First Commandblock adds a "1" Integer in every row where Result says "Non-Compliant". This seems to be fine.

Second Commandblock gives me an Error saying "A ring dependency was detected: Table[Non-Compliant], Table[Compliant], Table[Non-Compliant]."

Also third Commandblock gives me an Error saying "Only one column reference is accepted as an argument from the "DISCTINCTCOUNT" function."

Am I doing something wrong? 
If it helps: Best would be a list with each server I have in original table, without duplicates, and their compliance status (complaint or non-compliant)

[jdbuchanan71]

These are measures, are you trying to add them as calculated columns to a table?  If you create the first 2 measures you can add them to a pie chart to show the count.

[lynnsop]

Ouhh, sorry. Thats totaly the solution if I take those just as an measure.
I just thought that it would be cool to have a table listing the results. But thats absolutely enough! Thank you very much. 🙂

One last question out of curiousity. If I were to filter out some rows where Non-Compliancy is irrelavant, can I somehow exclude those specific rows dependend on a 3rd Row? With the basis of your solution/measures?

Example:
Server1 Non-Compliant    Job1
Server1 Compliant            Job2

Server1 Compliant            Job3

Server2 Non-Compliant   Job1

Server2 Non-Compliant   Job2

Server2 Compliant           Job3

So that Server1 would still be Compliant by excluding Job1 for the relevancy of the Compliancy Pie even though he has a Non-Compliant Job. Piechart should then still be at 50/50

[jdbuchanan71]

@lynnsop 

The measures will respect the filtering so my first answer will work if you filter out a job where that was the only job where the server was non-compliant it will then get counted as compliant.  A calculated column or separate table will NOT work if you want to apply other filters.