Trying to do Countif, with changing criteria

[andrewa521]

Hey all,

I have been browsing with no luck, I think I am trying to do something simple in excel, but cannot make it work.  Here is a sample data set.  Basically just trying to use DAX to do the countifs in the return column.  Then copy it down so my new column has the specific associated value for each row as shown below. 

The count if for, D2, in this case would be: =COUNTIFS($C$2:$C$9,">0",$B$2:$B$9,B2)

Class	Date	Value	Return
B	31-Jan	3	1
C	31-Jan	0	0
D	28-Mar	4	2
A	28-Mar	3	1
R	31-Jan	0	0
A	28-Feb	2	2
C	28-Feb	2	1
B	31-Jan	0	0

Thanks so much

[Ashish_Mathur · Accepted solution]

Hi,

Write this calculated column formula

Column = CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Date]=EARLIER(Data[Date])&&Data[Value]>0))

Hope this helps.

[andrewa521]

Thank you so much!  

[Ashish_Mathur]

You are welcome.

[Anonymous]

Hi @andrewa521 ,

I made a sample and then the formula returns the result as follows.

You will find that the formula result is different from the returned result you provided, and I will first give the metric written in DAX according to the result returned by the formula.

Measure = COUNTROWS(FILTER(ALLSELECTED('Table'),[Date]=MAX('Table'[Date])&&[Value]>0))

If you want to follow your return value based on your sample data provided, you can rewrite the following measure.

Measure 2 = var _count=COUNTROWS(FILTER(ALLSELECTED('Table'),[Date]=MAX('Table'[Date])&&[Value]>0))
return IF(MAX('Table'[Value])=0,0,_count)

Best Regards,

Stephen Tao

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