How to do a Count IF with condition using measure?

[Anonymous]

Hello, 

I need to get a count of employee numbers that are greater than 0 on Average variance, but I am getting an error. Can anybody please help me how to fix it? 

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 

[Ashish_Mathur · Accepted solution]

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.

[Ashish_Mathur · Accepted solution]

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]

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

[CNENFRNL · Accepted solution]

@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 )
    )
)

[Anonymous]

Awesome, thank you so much for the help. 

[edhans]

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. 

[Anonymous]

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