Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
Hi,
Write this calculated column formula
Column = CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Date]=EARLIER(Data[Date])&&Data[Value]>0))
Hope this helps.
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!
You are welcome.
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.