Forum Discussion

FruitBat's avatar
FruitBat
Frequent Visitor
6 years ago
Solved

Count Row with Multiple Criteria

Having difficulty counting the number of users with multiple group assignments.  Data look like that found in the table below.  Solution I seek is "1", indicating that there's a count of only one user - John - who's assigned to multiple groups (A, B, C).  In this instance, I don't want to count Ted and Bill; they're only assigned to one group.

 

Kind thanks!

 

Name

NameGroup
JohnGroup A
JohnGroup B
JohnGroup C
TedGroup A
Bill

Group A

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    6 years ago

    FruitBat Please create following measure to get count of name associated with more then one group

    Measure = 
VAR _groupCount = CALCULATE(COUNTX('Table','Table'[Group]),ALLEXCEPT('Table','Table'[Name]))
VAR _table = SUMMARIZE('Table','Table'[Name],"GroupCount",CALCULATE(COUNTX('Table','Table'[Group]),ALLEXCEPT('Table','Table'[Name])))
RETURN SUMX(FILTER(_table,[GroupCount]>1),[GroupCount])

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    FruitBat Please create following measure to get count of name associated with more then one group

    Measure = 
VAR _groupCount = CALCULATE(COUNTX('Table','Table'[Group]),ALLEXCEPT('Table','Table'[Name]))
VAR _table = SUMMARIZE('Table','Table'[Name],"GroupCount",CALCULATE(COUNTX('Table','Table'[Group]),ALLEXCEPT('Table','Table'[Name])))
RETURN SUMX(FILTER(_table,[GroupCount]>1),[GroupCount])
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi, FruitBat 

    You can first create a column to check whether the names match your conditions, then create a measure to distinct count ‘True’

    matched column = IF(CALCULATE(DISTINCTCOUNT('Table'[Group]), 
                      ALLEXCEPT('Table', 'Table'[Name])) > 1, TRUE(), FALSE())

count measure = CALCULATE(DISTINCTCOUNT('Table'[Column]), 'Table'[Column] = TRUE())

     

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