topic Re: Count rows from filtered measure in DAX Commands and Tips

Count rows from filtered measure

PaulPalkowski — Mon, 10 Feb 2020 15:55:26 GMT

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

Bear

Dog

Cat

Bird

Deer

Monkey

Cat

Bear

Beaver

Moose

I already have this

Re: Count rows from filtered measure

VasTg — Mon, 10 Feb 2020 16:36:17 GMT

PaulPalkowski

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

Re: Count rows from filtered measure

PPalkowski — Tue, 11 Feb 2020 13:36:44 GMT

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.

Re: Count rows from filtered measure

VasTg — Tue, 11 Feb 2020 15:14:12 GMT

PPalkowski

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

Re: Count rows from filtered measure

VasTg — Tue, 11 Feb 2020 15:21:04 GMT

PaulPalkowski PPalkowski

If I answered your question please mark it as a solution to close the topic.

Re: Count rows from filtered measure

PPalkowski — Tue, 11 Feb 2020 15:24:06 GMT

EXCELLENT!!!

Thank you so much..

hmmm, how to I mark this a 'the' solution...

😁

Re: Count rows from filtered measure

VasTg — Tue, 11 Feb 2020 15:32:41 GMT

PPalkowski

I guess you should see accept a solution button in my response near reply...

Re: Count rows from filtered measure

PPalkowski — Tue, 11 Feb 2020 15:48:41 GMT

I don't see anything

Re: Count rows from filtered measure

PPalkowski — Wed, 12 Feb 2020 20:45:16 GMT

I do not see ANY option to mark this as solved....

All I see is a thumb for Kudos , the number of kudos given and reply..

that's it

Re: Count rows from filtered measure

VasTg — Wed, 12 Feb 2020 20:58:13 GMT

PPalkowski

Maybe the thread starter can do it.. PaulPalkowski