Forum Discussion
How to do a Count IF with condition using measure?
Hi,
Try these measures:
Avg_Daily_Var._ % = DIVIDE([Avg_Daily_Submitted],[Avg_Daily_Reqquired],0)CountEmp# = COUNTROWS(FILTER(VALUES(Employee[firstlast]),[Avg_Daily_Var._ %] >0.0))Hope this helps.
Anonymous , you might want to try
CountEmp#_2 = COUNTROWS ( FILTER ( VALUES ( Employee[firstlast] ), [Avg_Daily_Var._ %] < 0.0 && [Avg_Daily_Var._ %] > -0.10 ) )or equally
CountEmp#_2 = COUNTROWS ( FILTER ( VALUES ( Employee[firstlast] ), AND ( [Avg_Daily_Var._ %] < 0.0, [Avg_Daily_Var._ %] > -0.10 ) ) )
Hi Anonymous , I would redo the logic a bit. First of all, redo your first measure:
Avg_Daily_Var._ % =
DIVIDE(
[Avg_Daily_Submitted],
[Avg_Daily_Reqquired],
0
)
DIVIDE is a safe divide and will return an alternate result (zero in this case) if the denominator is zero or some other error happens. Faster than IFERROR. You should never use the '/' symbol in DAX. Always use DIVIDE().
As to the other measure try this:
CountEmp# =
CALCULATE(
COUNT( Employee[firstlast] ),
Employee[SomeField] > 0
)
Where SomeField is a field that would need to have a value (vs 0) for there to be any average. Using a field within CALCULATE will work. Otherwise you'll need to replace the whole comparison with a FILTER() function. I'd need to have data though to really play with it, and that includes the formulas for your other measures, since you are embedding measure inside of measure inside of measure.
Hi, sorry, that I don't understand how Employee[SomeField] > 0 works. All I am trying to count the number of employees Employee[SomeField] > 0 and Employee[SomeField] < 0 (by the way this will be a different measure, not in the same measure). I am not sure how Employee[SomeField] > 0 get me that. Thank you