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 use...
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])
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])
FruitBat's avatar
FruitBat
Frequent Visitor
6 years ago

Nailed it.  Thanks, Anonymous !