First flag occurrence with filter and tiebreaking

[Anonymous]

Hello, I want to flag the first occurrence of accounts based on a date, with a filter (it should only consider if ACTIVE=yes), and if there are duplicates i need to choose only one of them (randomly).

Expected working is as below

Account	CreateDate	Active	FirstOccurrence
X	15-Mar-24	Yes	1
X	15-Mar-24	Yes	0
Y	14-Mar-24	No	0
Y	15-Mar-24	Yes	1

[v-weiyan1-msft]

Hi @Anonymous ,

Based on the sample and description you provided, please try the following steps:
My Sample:

1.You might consider adding an index column in Power Query.
The specific code is as follows.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WilDSUTI01fVNLNI1MgGyI1OLlWJ1CIibYYpHgsRNEOJ++QhhLMZEYjMmFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Account = _t, CreateDate = _t, Active = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Account", type text}, {"CreateDate", type date}, {"Active", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Account"}, {{"Count", each Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type)}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"CreateDate", "Active", "Index"}, {"CreateDate", "Active", "Index"})
in
    #"Expanded Count"

2.Use the following code to create calculated column.

FirstOccurrence = 
VAR CurrentAccount = 'Table'[Account]
VAR CurrentDate = 'Table'[CreateDate]
VAR MinDate =
    CALCULATE (
        MIN ( 'Table'[CreateDate] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Account] = CurrentAccount
                && 'Table'[Active] = "Yes"
        )
    )
VAR MinIndex =
    CALCULATE (
        MIN ( 'Table'[Index] ),
        FILTER (
            'Table',
            'Table'[Account] = CurrentAccount
                && 'Table'[CreateDate] = MinDate
        )
    )
RETURN
    IF ( 'Table'[CreateDate] = MinDate && 'Table'[Index] = MinIndex, 1, 0 )

Result is as below.

Is this the result you expect?

For further detail, please find attachment.

Best Regards,
Yulia Yan

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