Counting months that have a sum greater than zero

[PPalkowski]

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?

[Anonymous]

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 

[PPalkowski]

I get an error

[Anonymous]

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

[PPalkowski · Accepted solution]

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

Have a wonderful weekend!

[Anonymous]

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

[PPalkowski]

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

[PPalkowski]

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