Forum Discussion
Trying to do Countif, with changing criteria
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
Hi,
Write this calculated column formula
Column = CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Date]=EARLIER(Data[Date])&&Data[Value]>0))Hope this helps.
4 Replies
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.
- Ashish_Mathur
Super User
Hi,
Write this calculated column formula
Column = CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Date]=EARLIER(Data[Date])&&Data[Value]>0))Hope this helps.
Thank you so much!
- Ashish_Mathur
You are welcome.
