cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper V

## 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!

1 ACCEPTED SOLUTION
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).

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

Please mark my solution as accepted if it helped you!

3 REPLIES 3
Helper V

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

Solution Supplier

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/

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

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

Please mark my solution as accepted if it helped you!

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors