Calculated column

[Anonymous]

I need to create a calculated column that looks at ID, Type and Date in the table below, then: 
IF [type] = "121 meetings",
[Date] switch earliest for "1st 121 meeting", the second earliest date is switched to "2nd 121 meeting" and additional dates are "additional 121 meetings". 

ID	Type	Date		New column
1	121 meeting	01/02/2021		1st 121 meeting
1	121 meeting	03/02/2021		2nd 121 meeting
2	121 meeting	07/02/2021		2nd 121 meeting
2	121 meeting	02/02/2021		1st 121 meeting
3	121 meeting	09/02/2021		additional 121 meeting

Hope this makes sense, 

thanks in advance. 

[OwenAuger · Accepted solution]

If I understand your requirements correctly, the new column is based on the occurrence of "121 meeting" per ID.

If creating a DAX calculated column, something like this should work (returning blank if Data[Type] <> "121 meeting"):

New column DAX = 
IF (
    Data[Type] = "121 meeting",
    VAR MeetingDates = 
        CALCULATETABLE ( 
            VALUES ( Data[Date] ),
            ALLEXCEPT ( Data, Data[ID], Data[Type] ) -- Keep filters on ID and Type (must be "121 meeting")
        )
    VAR Occurrence = 
        RANKX ( MeetingDates, 'Data'[Date], , ASC )
    VAR Result = 
        SWITCH ( 
            Occurrence,
            1, "1st 121 meeting",
            2, "2nd 121 meeting",
            "additional 121 meeting"
        )
    RETURN
        Result
)

 By the way, for ID=3 should the column be "1st 121 meeting".

Regards,

Owen

[CNENFRNL · Accepted solution]

Hi, @Anonymous , you might want to try

Solution CC = 
VAR __topn =
    TOPN (
        2,
        FILTER (
            Schedule,
            Schedule[ID] = EARLIER ( Schedule[ID] ) && Schedule[Type] = EARLIER ( Schedule[Type] )
        ),
        Schedule[Date], ASC
    )
VAR __1st = MINX ( __topn, Schedule[Date] )
VAR __2nd = MAXX ( __topn, Schedule[Date] )
RETURN
    SWITCH (
        TRUE (),
        Schedule[Date] = __1st, "1ST",
        Schedule[Date] = __2nd, "2ND",
        "Additional"
    )

Personllay, I prefer to accomplish it in PQ; here's the snippet of M code for your reference,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0MlTITU0tycxLB/IMDPUNjPSNDIwMlWJ1sCowRlFghKnAnJACIxQFxpgKLDHcYGlpid+RaAqMCCnAdCReE7AoMCGgwJCQCYZIJsQCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Type = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Type", type text}, {"Date", type date}}, "Fr"),
    #"Added Custom" = Table.AddColumn(
        #"Changed Type",
        "Solution PQ",
        each 
        let
            dates = Table.Group(#"Changed Type", {"ID", "Type"}, {{"ar", each _}}){[ID=[ID], Type=[Type]]}[ar][Date],
            earliest = List.Min(dates),
            #"2nd ealiest" = List.MinN(dates, 2){1}?
        in
            if [Date]=earliest then "1st" else if [Date]=#"2nd ealiest" then "2nd" else "additional"
    )
in
    #"Added Custom"

[OwenAuger · Accepted solution]

If I understand your requirements correctly, the new column is based on the occurrence of "121 meeting" per ID.

If creating a DAX calculated column, something like this should work (returning blank if Data[Type] <> "121 meeting"):

New column DAX = 
IF (
    Data[Type] = "121 meeting",
    VAR MeetingDates = 
        CALCULATETABLE ( 
            VALUES ( Data[Date] ),
            ALLEXCEPT ( Data, Data[ID], Data[Type] ) -- Keep filters on ID and Type (must be "121 meeting")
        )
    VAR Occurrence = 
        RANKX ( MeetingDates, 'Data'[Date], , ASC )
    VAR Result = 
        SWITCH ( 
            Occurrence,
            1, "1st 121 meeting",
            2, "2nd 121 meeting",
            "additional 121 meeting"
        )
    RETURN
        Result
)

 By the way, for ID=3 should the column be "1st 121 meeting".

Regards,

Owen