Finding Duplicate Names with Different Status

[Anonymous]

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 converted?

Name	Hire Date	Status
Mary Jane	1/1/2019	Contractor
Mary Jane	1/1/2020	FTE
Gary Smith	11/1/2018	Contractor
Bob Ferguson	12/1/2016	Contractor
Bob Ferguson	06/01/2018	FTE

[Anonymous · Accepted solution]

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]

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 

[AlB]

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]

See it at work in the attached file

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

 Cheers 

[nandukrishnavs]

@Anonymous 

Try this measure

contractortofulltime = 
var _isContractor= CALCULATE(COUNT('Table'[Status]),'Table'[Status]="Contractor")
var _isFulltime= CALCULATE(COUNT('Table'[Status]),'Table'[Status]="Full Time")
var _contractortofulltime = IF(_isContractor>0&&_isFulltime>0,"Yes","No")
return _contractortofulltime

Now you can apply this measure in visual level filter ( is Yes ). 

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[nandukrishnavs]

@Anonymous  is it not working?