Forum Discussion

lynnsop's avatar
lynnsop
Helper I
5 years ago
Solved

Check for string in duplicate rows

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

Lets say I have this table:

Server1Compliant
Server1Compliant
Server1Compliant
Server2Compliant
Server2Non-Compliant
Server2Compliant


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! 🙂

Need Help
Tips and Tricks
  • jdbuchanan71's avatar
    jdbuchanan71
    5 years ago

    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.

6 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    5 years ago

    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's avatar
      lynnsop
      Helper I
      5 years ago

      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's avatar
        jdbuchanan71
        Super User
        5 years ago

        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.

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    5 years ago

    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.