Using SUM as a FILTER Condition

[jhd]

=CALCULATE(

DISTINCTCOUNT(MASTER_DATA[DATE]),

ALL(MASTER_DATA[DATE]),

MASTER_DATA[UNITS]>0))

My table of data (called MASTER_DATA)

DATE	UNITS
01/04/2017	-100
01/04/2017	100
05/04/2017	200
06/04/2017	150
07/04/2017	0

Result = 3

However, the result I want is 2, because I only want to count unique dates where the SUM of UNITS is greater than zero.

How can I filter by the SUM of UNITS? It doesn't seem to be possible to put SUM as a filter condition.

Thanks.

[Zubair_Muhammad · Accepted solution]

@jhd

Try this revision

Measure =
CALCULATE (
    DISTINCTCOUNT ( MASTER_DATA[DATE] ),
    FILTER (
        ALL ( MASTER_DATA[DATE] ),
        CALCULATE ( SUM ( MASTER_DATA[UNITS] ) ) > 0
    )
)

[jhd]

Thanks @Zubair_Muhammad, that worked perfectly!