Manager Count/Filter Code

[Anonymous]

Hi Folks,

I have the following dataset as an example: 

EE ID	Name	EE Type	Manager ID	Manager Name
11111	Bell, Taco	Contractor	22241	Frog, Kermit
11112	Jones, Jessica	Employee	22241	Frog, Kermit
22241	Frog, Kermit	Employee	65584	Blue, Gonzo
333544	Piggy, Miss	Employee	65656	Animal, Wild
65584	Blue, Gonzo	Employee	22241	Frog, Kermit
65656	Animal, Wild	Contractor	11114	Piano, Rowlf

We have a number of managers in our company as notated in the last column. Those managers are also employees as you can see in column 2 (I didn't build them all out). We also have two types of employees: employee and contractor. However, we do not consider a manager a "people manager" unless they are managing at least one employee.

What I want to do is get a DISTINCT COUNT of how many managers are managing EMPLOYEE's. So in this example, the count would be 3 because we have Kermit Frog, Gonzo Blue, and Wild Animal. Rowlf should not show up as part of the count because he's only managing a contractor. 

I know when I do a DISTINCTCOUNT DAX to get the number of managers by ID it gives me 4, which is great, but I want it to only give me 3 because Rowlf only has a contractor. 

I tried using DISTINCTCOUNT and FILTER but I can't seem to get it right. Any help would be great please. 

[Anonymous · Accepted solution]

Hi @Anonymous,

I created a measure

Count of Manager = CALCULATE(DISTINCTCOUNT(Employee[Manager ID]), FILTER(Employee, Employee[EE Type] = "Employee"))

I got count 3. Could you please try this and see if it works for you?

[Anonymous]

That is exactly what I needed. I was doing '&&' instead of "," between DISTINCTCOUNT and FILTER. Glad to know I wasn't far off. Thank you!

[Anonymous]

That is exactly what I needed. I was doing '&&' instead of "," between DISTINCTCOUNT and FILTER. Glad to know I wasn't far off. Thank you!