cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

How to count all duplicate and single values

Hi,

I would like to count the number of employee by department, and I use this formula

Attrition HC 2 = CALCULATE( COUNTROWS(except(values(Terminate[ID]),VALUES(Zero[Employee ID]))),Reasons_LookUp[Involuntary/Voluntary]="Voluntary")

Following is the example of the table Terminate[ID]

 Employee ID Department Status 12345 Finance Voluntary 12345 Finance Voluntary 23456 Account Voluntary 34567 Human Resources Voluntary 45678 Sales InVoluntary

Following is the example of the table Zero[Employee ID]

 Employee ID Department 99999 Marketing 88888 Marketing

However, the result i got is

Which it should actually be 2 in finance and the total should be 4.

Thank you

1 ACCEPTED SOLUTION
Super User

I see so you want to exclude Zero values.

With that in mind try this:

Measure = CALCULATE(COUNT('Table (2)'[Employee ID]),'Table (2)'[Status]="Voluntary",EXCEPT(VALUES('Table (2)'[Employee ID]),VALUES('Zero'[EID])))

Test: (no except)

With:

Proud to be a Super User!

4 REPLIES 4
Super User

Hi,

This should do what you want:

Measure = CALCULATE(COUNT('Table (2)'[Employee ID]),'Table (2)'[Status]="Voluntary")

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

Proud to be a Super User!

Helper I

Hi,

However, in my full data set, there are the employee ID in 'Terminate' table that can match with the 'Zero' table so I still need to contain  (except(values(

Super User

I see so you want to exclude Zero values.

With that in mind try this:

Measure = CALCULATE(COUNT('Table (2)'[Employee ID]),'Table (2)'[Status]="Voluntary",EXCEPT(VALUES('Table (2)'[Employee ID]),VALUES('Zero'[EID])))

Test: (no except)

With:

Proud to be a Super User!

Helper I

It works!

Thank you so muchhh.

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.