Count instances where Aggregated Sum is zero

[JBarrett3]

Hi all,

I have table like the below. I need to build a dynamic measure that will indicate if the total pump run hours per site for a given date is zero so that I can count or filter on sites with total zero hours. 

Source table:

Site	Date	Pump	Run_Hours
AAA	01/01/2019	1	0
BBB	01/01/2019	1	6
BBB	01/01/2019	2	0
CCC	01/01/2019	1	4
DDD	01/01/2019	1	0
DDD	01/01/2019	2	0

Desired output:

Site	Date	Zero_Hour_Sites
AAA	01/01/2019	1
BBB	01/01/2019	0
CCC	01/01/2019	0
DDD	01/01/2019	1
Total		2

I have found a similar requirement here where the proposed solution was something along the lines of:

Measure =
COUNTROWS (
DISTINCT ( CALCULATETABLE ( VALUES ( Table[Site] ), Table[Run Hours] = 0 ) )
)

But I am not sure how to enhance this to group by both Site and Date?

Many thanks.

Jeanette

[PattemManohar]

@JBarrett3  Please try this as New Measure.

Test308 = COUNTROWS(DISTINCT(EXCEPT(CALCULATETABLE(VALUES(Test308Filter[Site]),Test308Filter[Run_Hours]=0),CALCULATETABLE(VALUES(Test308Filter[Site]),Test308Filter[Run_Hours]>0))))+0

[JBarrett3]

Thank you so much.

That works great for 1 day, but when I introduce a second date the total no longer works. Any idea how to resolve that?

[PattemManohar · Accepted solution]

@JBarrett3 Ok, please update the Measure logic as below

Test308 = 
VAR _Zero = DISTINCT(SELECTCOLUMNS(FILTER(Test308Filter,Test308Filter[Run_Hours]=0),"Site",[Site],"Date",[Date]))
VAR _NonZero = DISTINCT(SELECTCOLUMNS(FILTER(Test308Filter,Test308Filter[Run_Hours]>0),"Site",[Site],"Date",[Date]))
RETURN COUNTROWS(DISTINCT(EXCEPT(_Zero,_NonZero)))+0