[DAX] Help detecting the last matching substring from another column

Hi akakak ,

Please try:

First, create a duplicate table and split columns into rows:

Then add index column:

Here is the M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZhbbiQhDEW3EvV3pIDNy/M3C5gVRNkJix/S4AudTrsszShV1LmFscF29efnjW/vN/mIHxSI3iL9CWH8e/v7bwzH8b/Hzvc/t6/3z1u5hIv++cZjfc3TBKnfoe+ru4SKYRATlRBVOO/6VOidvoepvX6PGtknBXs50WuRpNSSCu83ffJ74P6Saq1gGT+Z5dRaksPUSW1To1wHY0FrnhQcli1I480hX4d8QksSicmwLJdWArxGwpJin4NdpccjvNLYScsMpdSOLGz4VUWLUlFpbPgIxim3d3pLIh7h4iBMMQVj0+GUKIcNnlpgz4zK7RmFYrjeB4tajsmlBiMEmE05zFZkGOBYn3JYX02BPUdYOcw4BpIx4ylM4VEYoxF8mKocTG1C1djy+rjj4q6SFOR6Vyu14iA15OvgKQURV8dMi9KjEKuUeH2AFMPxj0yGbKd9BeHGyBzL9eKA6YzD7Gi4f589Bfep5dqyJ+QAD2Nby64Tr+CeNY9UZbhIKlHWmIzLDoHer9dQKJ4zCfCwgFu9rgXAMOO4t3L7lOUOsG9jS0ieGRcGWY3JcNWxxgXuNZZgGqszKoYKEJqVsA5ZOzdhocCerAPwMHSMuNao4CHlIEaBPqQLPKRllD7HOhe2C2S16tVZISvq/JIKic9FCzyl4ioGAI8WNHPw9FcAD2lhchms4CGtYnVOh3SBWyq1NEfeVUwDIxKD4SLIFnbKXGlMQRg62q2Wr7tDYGtG4pLzdWMGDLKWgycYALehuURXHAEe0hblOhjA1NhSk6PiAoNMSjUOF2QLU1kNsXpKLsD9tVU5shHDQ7rAUyqOag1MjW0hVSP83EYfosewjJWGPoc6lN8fNnNoAuu9lay8vYOs4A6yjKVdp0JguhLhYuVtPO/7agklJ2N3oIwCRBnl0IJ4shnA/cEYhJKRIMan12jloB+JKeU+Bzu0z8/WxyiRK8kCPL5ja3VVP4BbOnohz0ewYurDYbcVbtMRqn3liNHgO9ocYDCpsSu5ATycMFo0T/cL8EFqfSlhHwLc+5BaK0bemJ558tMSHYPzbTz6GqMIqdcUUyM4tmAs/XcjVPRsRGyuXgrgdiQTWzF4YcgSPRtCYv1Qg5wMcP/oxaPn8q1hgccaxjejp7EDeEqLdaAS1URJrZ53HSId2GsYX6+eagbwUWqkoUdp+im1yoeZFFT7Kimk2Op1awZMt3caieu6wALb10tdrKZnqwuFh0nF+tX2962soqetPB5YRpheVe1Lr47n130ksGOB1o+Je58ruPd54pQt35irWdrfVvP1Hw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [employee_id = _t, Date = _t, manager_id = _t, Path = _t, Matches_From_Another_Table = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"employee_id", Int64.Type}, {"Date", type datetime}, {"manager_id", Int64.Type}, {"Path", type text}, {"Matches_From_Another_Table", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Path", Splitter.SplitTextByDelimiter("|", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Path"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Path", Int64.Type}}),
    #"Split Column by Delimiter1" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type1", {{"Matches_From_Another_Table", Splitter.SplitTextByDelimiter("|", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Matches_From_Another_Table"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Matches_From_Another_Table", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type2", each ([Path] <> null) and ([Matches_From_Another_Table] <> null)),
    #"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 1, 1, Int64.Type)
in
    #"Added Index"

Output:

Then add a new column for your original table, here is the DAX:

Column =
VAR _a =
    SELECTCOLUMNS (
        FILTER ( 'Table (2)', [employee_id] = EARLIER ( 'Table'[employee_id] ) ),
        "a", [Matches_From_Another_Table]
    )
VAR _b =
    MAXX (
        FILTER (
            'Table (2)',
            [employee_id] = EARLIER ( 'Table'[employee_id] )
                && [Path] IN _a
        ),
        [Index]
    )
RETURN
    CALCULATE ( MAX ( 'Table (2)'[Path] ), FILTER ( 'Table (2)', [Index] = _b ) )

Final output:

Best Regards,

Jianbo Li

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