topic Re: How to count all duplicate and single values in DAX Commands and Tips

How to count all duplicate and single values

Anonymous — Tue, 10 Oct 2023 05:01:00 GMT

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.

Please help me 🙏

Thank you

Re: How to count all duplicate and single values

ValtteriN — Tue, 10 Oct 2023 05:31:39 GMT

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!

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

Re: How to count all duplicate and single values

Anonymous — Tue, 10 Oct 2023 08:09:48 GMT

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(

Re: How to count all duplicate and single values

ValtteriN — Tue, 10 Oct 2023 08:25:41 GMT

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:

Re: How to count all duplicate and single values

Anonymous — Tue, 10 Oct 2023 10:02:34 GMT

It works!

Thank you so muchhh.