Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

First flag occurrence with filter and tiebreaking

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

 

AccountCreateDateActiveFirstOccurrence
X15-Mar-24Yes1
X15-Mar-24Yes0
Y14-Mar-24No0
Y15-Mar-24Yes1
Need Help
Show and Tell
Tips and Tricks

1 Reply

  • v-weiyan1-msft's avatar
    v-weiyan1-msft
    Icon for Community Support rankCommunity Support
    2 years ago

    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.