Forum Discussion
How to do a Count IF with condition using measure?
Avg_Daily_Var._ % = IFERROR([Avg_Daily_Submitted]/[Avg_Daily_Reqquired],0)
CountEmp# = CALCULATE(COUNT(Employee[firstlast]), [Avg_Daily_Var._ %] >0.0)
Error - A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.
I tried adding a filter that did not work. I want to create a measure, not a calculated column.
My sample data looks like,
Any help will be highly appreciated
Thank you
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 ) ) )
6 Replies
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.
HI, thank you so much. This is the one I am looking for but what happens if I have more than one argument. Please see below
CountEmp#_2 = COUNTROWS(FILTER(VALUES(Employee[firstlast]),([Avg_Daily_Var._ %] < 0.0 AND [Avg_Daily_Var._ %] > -0.10))I tried it, did not work. Can you please tell me how to add more than 1 condition on measures?
Thank you so much
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
