Identify duplicates in a column, then populate a new column based on evaluation of a 3rd column

[MuppetyMe]

See sample data below. Trying to create a "helper column" to detect duplicates in the ID column and transfer data from the Name column, but only if the Qualified column has a value of Yes. Ideally, if there is a single Name with a Yes condition for an ID, that Name is brought over; if there are multiple Names, assign a value of "Multiple," and if Qualified is No, leave the field blank.

Used something similar to the solution in another post (@edhans), but not sure how, where, or if I can tuck in a filter somewhere to only evaluate rows with the Qualified = Yes condition. 

Solved: Find duplicates in a column and give a value - Microsoft Fabric Community

Ultimate purpose is to create a FIRSTNONBLANK statement in a column on another table. 

ID	Name	Qualified
ABC1	Bob	Yes
ABC1	Candice	No
ABC1	Kevin	No
ABC1	Rahul	No
ABC1	Viktor	No
ABC2	Benoit	No
ABC2	Joe	Yes
ABC2	Kyler	No
ABC2	Sam	Yes
ABC2	Viktor	No
ABC3	Benoit	No
ABC3	Bob	Yes
ABC3	Joe	Yes
ABC3	Roger	No
ABC3	Sam	Yes
ABC4	Benoit	No
ABC4	Candice	No
ABC4	Kevin	No
ABC4	Kyler	No
ABC4	Viktor	No
ABC5	Candice	No
ABC5	Joe	Yes
ABC5	Kyler	No
ABC5	Tom	No
ABC5	Viktor	No

[TheoC · Accepted solution]

Hi @MuppetyMe 

Hopefully I've understood your requirement.

You can create the following Calculated Column to get the output I believe you are wanting.  If you have any questions or want clarification, please let me know.

I've also attached a PBIX file for you to assist.

HelperColumn = 

VAR CurrentID = 'Table'[ID]

VAR CurrentName = 'Table'[Name]

VAR QualifiedPeople =

    FILTER (
        ALL ( 'Table' ) ,
        'Table'[ID] = CurrentID && 'Table'[Qualified] = "Yes"
    )

VAR QualifiedCount = COUNTROWS ( QualifiedPeople )

VAR QualifiedPersonName =

    IF ( QualifiedCount = 1 , MAXX ( QualifiedPeople , 'Table'[Name] ) , BLANK() )

VAR IsQualified = 'Table'[Qualified] = "Yes"

RETURN

SWITCH ( 
    TRUE() ,
        QualifiedCount = 1 && IsQualified , QualifiedPersonName ,
        QualifiedCount > 1 && IsQualified , "Multiple" ,
    BLANK()
)

The output will be as per below.

Thanks heaps and hope this helps!

Theo 🙂

[MuppetyMe]

THANK YOU @TheoC!!! This worked great. Sending you a virtual hug 🤗
Cheers!

[TheoC]

Haha! @MuppetyMe you're an all-star! Massive virtual hugs back to you, too! 

I'm glad it was what you were after!

All the best! 😄

Theo