Forum Discussion
Count instances where Aggregated Sum is zero
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
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
3 Replies
- PattemManohar
Community Champion
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
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
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
