Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Finding Duplicate Names with Different Status

Hi,    I have a requirement to showcase on a table employees who went from contractor to full time. How can I achieve this? Is there a DAX to create a count or highlight those names which converte...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous ,

     

    Create a measure like below and add it to visual filter then configure the condition formatting like below.

    Measure = CALCULATE(DISTINCTCOUNT('Table'[Status]),ALLEXCEPT('Table','Table'[Name]))

    Result would be shown as below.

     

    Best Regards,

    Jay

     

    Community Support Team _ Jay Wang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

AlB's avatar
AlB
Community Champion
6 years ago

Hi Anonymous 

You can create a new table:

Converted =
FILTER (
    DISTINCT ( Table1[Name] );
    VAR LastDate_ =
        CALCULATE ( MAX ( Table1[Hire Date] ) )
    VAR IsFTEOnLastDate_ =
        CALCULATE (
            COUNT ( Table1[Hire Date] );
            Table1[Hire Date] = LastDate_;
            Table1[Status] = "FTE"
        ) > 0
    VAR WasPreviouslyContractor_ =
        CALCULATE (
            COUNT ( Table1[Hire Date] );
            Table1[Hire Date] <= LastDate_;
            Table1[Status] = "Contractor"
        ) > 0
    RETURN
        AND ( IsFTEOnLastDate_; WasPreviouslyContractor_ )
)

where Table1 is the table you show

Please mark the question solved when done and consider giving kudos if posts are helpful.

 Cheers 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    • AlB's avatar
      AlB
      Community Champion
      6 years ago

      You need to use the name of your table in lieu of Table1 and perhaps commas instead of ";" depending on your locale

      Please mark the question solved when done and consider giving kudos if posts are helpful.

       Cheers 

      • AlB's avatar
        AlB
        Community Champion
        6 years ago

        See it at work in the attached file

        Please mark the question solved when done and consider giving kudos if posts are helpful.

         Cheers