Distinct count grouping measure

[dev85]

Hello guys!

I have the next table

Client name	Compliance
A	YES
A	YES
B	NO
C	YES
D	YES
E	YES
E	YES
A	NO

I need to set up a measure with a distinc count on the client names based on the compliance value, this means that only count a client if all the grouped compliance answers are = "Yes". For example, for this case the result is 3 because only C, D and E compliances are all "Yes", A and B are not counted because the only reply for B is "No" and for A, one of the submits is also "No".

Thanks in advance for the help!

[v-zhangti · Accepted solution]

Hi, @dev85 

You can try the following methods.

Measure =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Client name] ),
    FILTER (
        ALL ( 'Table' ),
        CALCULATE (
            MIN ( 'Table'[Compliance] ),
            ALLEXCEPT ( 'Table', 'Table'[Client name] )
        ) = "YES"
    )
)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[dev85]

It worked perfectly! I just remove the ALL function because I needed for the measure to be dynamic according to the page filters. 
Thank you so much!

[Greg_Deckler]

@dev85 Maybe:

Measure =
  VAR __YesTable = DISTINCT(SELECTCOLUMNS(FILTER('Table',[Compliance] = "YES"),"__Client",[Client name]))
  VAR __NoTable = DISTINCT(SELECTCOLUMNS(FILTER('Table',[Compliance] = "NO"),"__Client",[Client name]))
  VAR __Table = EXCEPT(__YesTable, __NoTable)
  VAR __Result = COUNTROWS(__Table)
RETURN
  __Result