Compare text from same column in DAX

[ncbshiva]

Hi All,

Below is my sample data and i want to report the number of joiners who are also leavers.

For example : Employee ID = 24521 has type with "Joiner" and "Leaver". I want to list this employee id who has type both Joiner and Leaver.

Employee ID	Type
23544	Joiner
24521	Joiner
34664	Joiner
87627	Leaver
61536	Leaver
86262	Joiner
24521	Leaver
34664	Leaver

Regards

Shiva

[Nathaniel_C · Accepted solution]

Hi @ncbshiva ,
Try this:

Joiner and Leaver =
VAR _cnt =
    CALCULATE ( COUNTROWS ( 'Table' ), ALLEXCEPT ( 'Table', 'Table'[Employee ID] ) )
RETURN
    IF ( _cnt = 2, MAX ( 'Table'[Employee ID] ) )

 
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel