Count of Positions

[tdupton]

I'm trying to create a measure to count employees by position.  The data is coming from a SQL view.  It seems straightforward but all of my counts are way off.  I've used countrows, counta, count...all with the exact same results.  What DAX counts is way less that what is actually present in the datasource.

Count of Managers = CALCULATE(COUNTROWS(qryActiveEmployees),qryActiveEmployees[Position]="Manager")

 

[Aron_Moore]

Try count instead of countrows. You may want the explicit filter too.

Either

Count of Managers = CALCULATE(COUNT(qryActiveEmployees[Nickname]),qryActiveEmployees[Position]="Manager")

or

Count of Managers = CALCULATE(COUNT(qryActiveEmployees[Nickname]),FILTER(qryActiveEmployees,qryActiveEmployees[Position]="Manager"))

[tdupton]

Count of Managers = CALCULATE(COUNT(qryActiveEmployees[FirstName]),FILTER(qryActiveEmployees,qryActiveEmployees[Position]="Manager"))

I've tried all of the variations. They all return 1 manager when, in fact, there are 11 in the datasource.  I can view the dataset and filter by the Position field and see all of the managers just fine.  No matter what position I choose, DAX returns way less than the datasource shows.

I thought it could be a whitespace problem but I've already applied Clean and Trim.  Is this some kind of bug?  I shouldn't have to group them first in order to count them, right?

[Ashish_Mathur]

Hi,

That formula should be a measure, not a calculated column.  Furthermore, you do not need the FILTER function.  Try this measure

= CALCULATE(COUNT(qryActiveEmployees[FirstName]),qryActiveEmployees[Position]="Manager")

Hope this helps.

[tdupton]

Thanks, everyone.  As it turns out the formula worked all the time.  It was actually a relationship that was filtering the data on my report.  I had a relationship between the hire date from the employee table and a calendar table.  I'm still not certain why it was filtering out results, but I'm going to investigate that issue.

Thanks for the quick replies.