Forum Discussion

jhd's avatar
jhd
Helper I
7 years ago
Solved

Using SUM as a FILTER Condition

=CALCULATE(

DISTINCTCOUNT(MASTER_DATA[DATE]),

ALL(MASTER_DATA[DATE]),

MASTER_DATA[UNITS]>0))

 

My table of data (called MASTER_DATA)

DATEUNITS
01/04/2017-100
01/04/2017100
05/04/2017200
06/04/2017150
07/04/20170

 

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.

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    jhd

     

    Try this revision

     

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

2 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    7 years ago

    jhd

     

    Try this revision

     

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