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
JULY1
Helper I
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 IDDepartmentStatus
12345FinanceVoluntary
12345FinanceVoluntary
23456Account Voluntary
34567Human ResourcesVoluntary
45678SalesInVoluntary

 

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

Employee IDDepartment
99999Marketing
88888Marketing

 

 

However, the result i got is

JULY1_2-1696913852680.png

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

 

Please help me 🙏

Thank you

1 ACCEPTED SOLUTION

I see so you want to exclude Zero values. 

With that in mind try this:

ValtteriN_0-1696926123096.png

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


Test: (no except)

ValtteriN_2-1696926280623.png

With:

ValtteriN_3-1696926336444.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
ValtteriN
Super User
Super User

Hi,

This should do what you want:

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

ValtteriN_0-1696915883601.png

 

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!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi,

 

Thank you for your answer.
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( 

I see so you want to exclude Zero values. 

With that in mind try this:

ValtteriN_0-1696926123096.png

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


Test: (no except)

ValtteriN_2-1696926280623.png

With:

ValtteriN_3-1696926336444.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




It works!

Thank you so muchhh.

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.

Top Solution Authors