topic Re: Counting months that have a sum greater than zero in DAX Commands and Tips

Counting months that have a sum greater than zero

PPalkowski — Wed, 12 Feb 2020 21:01:22 GMT

I have this table

Animal	Fed
Lion	2/2/2020
Lion	2/5/2020
Bear
Dog	2/2/2020
Cat
Bird	1/30/2020
Bird	2/2/2020
Deer	1/30/2020
Monkey
Monkey
Cat
Bear
Beaver	1/2/2020
Beaver	1/3/2020
Beaver	2/2/2020
Moose

and this visual

Animal	January	February	Total
Bear	0	0	0
Beaver	2	1	3
Bird	1	1	2
Cat	0	0	0
Deer	1	0	1
Dog	0	1	1
Lion	0	2	2
Monkey	0	0	0
Moose	0	0	0

what I need is if an animal was fed at least once that month, signify that with an x and count the x's as shown

Animal	January	February	Count
Bear	0	0	0
Beaver	x	x	2
Bird	x	x	2
Cat	0	0	0
Deer	x	0	1
Dog	0	x	1
Lion	0	x	1
Monkey	0	0	0
Moose	0	0	0

How can I do this?

Re: Counting months that have a sum greater than zero

Anonymous — Wed, 12 Feb 2020 21:32:28 GMT

Hi PPalkowski ,

You can use something like this:

Assumming you have a date table somewhere.

AnimalFed = 
switch(
 true(),
 ISFILTERED(Dates[MonthName]),if (ISBLANK(count(Animals[Fed])),0,"x"),
 count(Animals[Fed]) 
 )

Hope this helps,

Jan

if this is a solution for you, don't forget to mark it as such. thanks

Re: Counting months that have a sum greater than zero

PPalkowski — Thu, 13 Feb 2020 13:07:31 GMT

I don't have a date table, all I have for dates are in the fed column

Animal	Fed
Lion	2/2/2020
Lion	2/5/2020
Bear
Dog	2/2/2020
Cat
Bird	1/30/2020
Bird	2/2/2020
Deer	1/30/2020
Monkey
Monkey
Cat
Bear
Beaver	1/2/2020
Beaver	1/3/2020
Beaver	2/2/2020
Moose

I created one, names Dates

MONTH

January

February

March

April

May

June

July

August

September

October

November

December

It's getting there, but the dates are out of order and not the matrix isn't calcualting correctly, The Lion was fed 2 times in Februaury, so Februaury would get and X, and the total would be 1, it shows an X in every month and a total of 2

Re: Counting months that have a sum greater than zero

PPalkowski — Thu, 13 Feb 2020 15:10:48 GMT

I have the dates all sorted,

your formula worked perfectly

AnimalFed = switch( true(), ISFILTERED(Data[Fed].[Month]),if (ISBLANK(count(Data[Fed])),0,"x"), count(Data[Fed]) )

All I need are 'working' totals and I am good.

even If I put a 1 in the formula, instead of an x

It's still not calculating correctly

Re: Counting months that have a sum greater than zero

Anonymous — Thu, 13 Feb 2020 18:40:18 GMT

Sorry I missed the detail where the total only needs to reflect the distinct months

AnimalFed = 
switch(
 true(),
 ISFILTERED(Dates[MonthName]),if (ISBLANK(count(Animals[Fed])),0,"x"),
 COUNTROWS( groupby ( Animals, Animals[Animal], Dates[MonthName] ) ) 
 )

the countrows should get the required results. It creates a table per animal per month and then counts the rows.

Jan

Re: Counting months that have a sum greater than zero

PPalkowski — Thu, 13 Feb 2020 19:07:33 GMT

I get an error

Re: Counting months that have a sum greater than zero

Anonymous — Fri, 14 Feb 2020 06:50:46 GMT

Ok, not having a date table, you need to create a Calculated Column in your data table:

MonthColumn = month('Data'[Fed])

the measure will then be:

AnimalFed = 
switch(
 true(),
 ISFILTERED(Data[MonthName]),if (ISBLANK(count(Data[Fed])),0,"x"),
 COUNTROWS( groupby ( Data, Data[Animal], Data[MonthColumn] ) ) 
 )

Jan

if this is a solution for you, don't forget to mark it as such. thanks

Re: Counting months that have a sum greater than zero

PPalkowski — Fri, 14 Feb 2020 14:27:09 GMT

I have everything I need, YOU have been an AWESOME resource!!

Have a wonderful weekend!