Forum Discussion

dev85's avatar
dev85
Frequent Visitor
3 years ago
Solved

Distinct count grouping measure

Hello guys!

 

I have the next table

 

Client nameCompliance
AYES
AYES
BNO
CYES
DYES
EYES
EYES
ANO

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!

Need Help
  • v-zhangti's avatar
    v-zhangti
    3 years ago

    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.

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    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
  • v-zhangti's avatar
    v-zhangti
    Community Support
    3 years ago

    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's avatar
      dev85
      Frequent Visitor
      3 years ago

      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!