Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
spagad6263
Post Patron
Post Patron

How to count number of employees of each department?

Hi,

 

I am new to DAX, and hope someone can provide advice how to use DAX?

 

Below are 2 tables - [Employee] and [Department], and I would like to have a new measure (or column) in Department table to show number of employee of each department (highlighted in grey). Any suggestions are most appreciated!

 

spagad6263_0-1685333622196.png

 

1 ACCEPTED SOLUTION
Dhairya
Solution Supplier
Solution Supplier

There are two methods to calculate count of employees in each department.

Method 1:
step1:
Create table visual and add dept id(from employee table), dept name(from department table) and employee id(from employee table).
Dhairya_0-1685335993931.png
step2:
summarize employee id as Count or distinct count (according to your data).


Method 2: (Using Measure/Calculated column)
DAX Code :
 

 

Number of employees =
CALCULATE (
COUNT ( Employee[Employee_id] ),
ALLEXCEPT ( Employee, Employee[Dept_id], Department[Dept_Name] )
)

 

Dhairya_0-1685336894683.png

Please mark my solution as accepted if it helped you!

View solution in original post

3 REPLIES 3
spagad6263
Post Patron
Post Patron

Perfect and this is exactly what I need. I used the second method, and it works! Thanks so much for the help.

 

Are you able to have a brief explanation about below?

ALLEXCEPT ( Employee, Employee[Dept_id], Department[Dept_Name] )

 

 

here we have used ALLEXCEPT as modifier, which will remove all the filters from table specified in the first argument and keeps only the filters on the columns specified in the following arguments.

 

You can refer following link to better understand how allexcept can be used in different ways.
https://dax.guide/allexcept/

Dhairya
Solution Supplier
Solution Supplier

There are two methods to calculate count of employees in each department.

Method 1:
step1:
Create table visual and add dept id(from employee table), dept name(from department table) and employee id(from employee table).
Dhairya_0-1685335993931.png
step2:
summarize employee id as Count or distinct count (according to your data).


Method 2: (Using Measure/Calculated column)
DAX Code :
 

 

Number of employees =
CALCULATE (
COUNT ( Employee[Employee_id] ),
ALLEXCEPT ( Employee, Employee[Dept_id], Department[Dept_Name] )
)

 

Dhairya_0-1685336894683.png

Please mark my solution as accepted if it helped you!

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.