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: Server1 Compliant Server1 Compliant Server1 Compliant Server2 Compliant Server2 N...
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.

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.

    • lynnsop's avatar
      lynnsop
      Helper I
      5 years ago

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

        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.