Count with Sum

[jmcph]

Hi everyone! 

I am asking for help with some counting function, i have a table as follows: 

Name            Date                     Amount

ABC                1/1/2020            100
DEF                 1/5/2020            50
ABC                1/ 20/2020          60

I am trying to compute for how many Distinctcount NAME is less than 200 (amount) for January. The closest Count that i get from my dax measures was (2) which counted both amount as < 200. What i need it to do is for it to sum both values of the same month and give me a count of the name, which in this case should be One (1) only. 

What kind of measure should i use for this specific task? Any help and advice will be much appreciated!!! 

Thank you! 

[v-lionel-msft · Accepted solution]

Hi @jmcph ,

Please refer to the formula.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ryan_mayu]

@jmcph 

Not clear about your request.  ABC = 100+60, DEF=50, both are less than 200. So the value of measure is 2. Could you please elaborate your request?

[amitchandak]

@jmcph , You can use date Slicer for Jan 2002

Try a measure like

calculate(distinctcount(Table[name]), filter(Table, Table[Amount]<200))

Hardcoded Jan

calculate(distinctcount(Table[name]), filter(Table, Table[Amount]<200 && format(table[Date],"YYYYMM") ="202001"))

[jmcph]

Thanks! I already tried this measure, but it still shows the count of (2) but when i added Values(TableColumn) on the Filter then it shows the result that i wanted. Thanks again!