Forum Discussion

ncbshiva's avatar
ncbshiva
Icon for Advocate V rankAdvocate V
6 years ago
Solved

Compare text from same column in DAX

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 IDType
23544Joiner
24521Joiner
34664Joiner
87627Leaver
61536Leaver
86262Joiner
24521Leaver
34664Leaver

 

Regards

Shiva

Need Help
  • Nathaniel_C's avatar
    Nathaniel_C
    6 years ago

    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

1 Reply

  • Nathaniel_C's avatar
    Nathaniel_C
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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