Forum Discussion
Count rows from filtered measure
I have tried and tried to look for a solution, and didn't find anything that wasn't confusing. I must be over thinking this...
Here is my data, it's so very simple...only one column named Animal, with 16 rows. I am looking for a measure that returns the distinct count of animals that are on the list once, and a distinct count of animals that are on this list more than once. So in this instance, that are a total of 9 different animals, 3 animals are listed once and 6 are listed more than once.
| Animal |
| Lion |
| Lion |
| Bear |
| Dog |
| Cat |
| Bird |
| Bird |
| Deer |
| Monkey |
| Monkey |
| Cat |
| Bear |
| Beaver |
| Beaver |
| Beaver |
| Moose |
I already have this
Try this measures...
Single_Occurrence = VAR A = SUMMARIZE('Table (2)','Table (2)'[Animal],"Count1",COUNT('Table (2)'[Animal])) RETURN CALCULATE(COUNT('Table (2)'[Animal]),FILTER(a,[Count1]=1))Multiple_Occurrence = VAR A = SUMMARIZE('Table (2)','Table (2)'[Animal],"Count1",COUNT('Table (2)'[Animal])) RETURN CALCULATE(COUNT('Table (2)'[Animal]),FILTER(a,[Count1]>1))If it helps, mark it as a solution
Kudos are nice too
9 Replies
Try this measures...
Single_Occurrence = VAR A = SUMMARIZE('Table (2)','Table (2)'[Animal],"Count1",COUNT('Table (2)'[Animal])) RETURN CALCULATE(COUNT('Table (2)'[Animal]),FILTER(a,[Count1]=1))Multiple_Occurrence = VAR A = SUMMARIZE('Table (2)','Table (2)'[Animal],"Count1",COUNT('Table (2)'[Animal])) RETURN CALCULATE(COUNT('Table (2)'[Animal]),FILTER(a,[Count1]>1))If it helps, mark it as a solution
Kudos are nice too
Thank you so very much, the first formula, works wonderful and returns 3 as expected, the second formula returns 13 and not 6. I am trying to get the count of the unique animals that are listed more than once. In this case, 6 is the count I am looking for.
Replace the count with distinctcount in the return statement.
Multiple_Occurrence = VAR A = SUMMARIZE('Table (2)','Table (2)'[Animal],"Count1",COUNT('Table (2)'[Animal])) RETURN CALCULATE(DISTINCTCOUNT('Table (2)'[Animal]),FILTER(a,[Count1]>1))If it helps mark it as a solution
Kudos are nice too
