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

[akakak]

Hello, I am having struggling with this dax question and I was wondering if someone faced a similar problem and could help me.

I basically have 2 columns:

The first one is an hierarchy that I create it with the "Path" function.

The second one are all the matching values from the column "Path" with another table of the model.

As you might know, the function Path returns the path ordered from upper to down (an ordered hierarchy). The second formula from the column "Matches_From_Another_Table", returns all the matching values of another table. But it obviously doesn't do it in an ordered manner (is not possible to order that table as in an organization chart, each team has their own path).

What I am looking for is a way to return the last value from the column "Path" matching some value of the column "Matches_From_Another_Table".

I leave attached the model as maybe the formula from "Matches_From_Another_Table" can be improved.

PS: Retrieving the maximum value is not valid because it id might be or not the biggest one.

PS2: The business case is that I have an hierarchy of all the employees of the company. Also, I have another table specifiying the managers that create an area by themselves. So, I want to find the most inmediate employee's manager that creates an area, so I can assign them to an specific team.

The file:

https://drive.google.com/file/d/1NI1KA7wl69hL2AuAHbBCMkg---MettbN/view?usp=sharing

Thank you very much! 

PS3: 

[v-jianboli-msft · Accepted solution]

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.

[akakak]

Holy cow that's super good and it works fast!

Thank you very much.

I needed to deliver it this monday so at the end wat I did was to create ficticial columns ( I created like 25 variables) seperating every number between commas. Every record was a number, then comparing the columns with the ones appearing in the path and retrieving only the first one to appear. Down below is not all the code, only the first variable. You can imagin how slow it was, so I'll substitute it straight away.

Thank you very much master!

VAR Path_01= VALUE(PATHITEM ( SUBSTITUTE ( Output_Salaries_Date_Max[Path]; "-"; "|" ); 1 ))

VAR Match_01= VALUE (PATHITEM ( SUBSTITUTE ( Output_Salaries_Date_Max[Matches_From_Another_Table]; "-"; "|" ); 1 ) )

VAR Reorder_01= VALUE(SWITCH (TRUE ();Match_01 = Path_01; Path_01;Match_02 = Path_01; Path_01;Match_03 = Path_01; Path_01;Match_04 = Path_01; Path_01;Match_05 = Path_01; Path_01;Match_06 = Path_01; Path_01;Match_07 = Path_01; Path_01;Match_08 = Path_01; Path_01;Match_09 = Path_01; Path_01;Match_10 = Path_01; Path_01;Match_11 = Path_01; Path_01;Match_12 = Path_01; Path_01;Match_13 = Path_01; Path_01;Match_14 = Path_01; Path_01;Match_15 = Path_01; Path_01;Match_16 = Path_01; Path_01;Match_17 = Path_01; Path_01;Match_18 = Path_01; Path_01;Match_19 = Path_01; Path_01;Match_20 = Path_01; Path_01;Match_21 = Path_01; Path_01;Match_22 = Path_01; Path_01;Match_23 = Path_01; Path_01;Match_24 = Path_01; Path_01;Match_25 = Path_01; Path_01;0) )

VAR Final_Calculation= SWITCH (TRUE ();Reorder_25 <> 0; Reorder_25;Reorder_24 <> 0; Reorder_24;Reorder_23 <> 0; Reorder_23;Reorder_22 <> 0; Reorder_22;Reorder_21 <> 0; Reorder_21;Reorder_20 <> 0; Reorder_20;Reorder_19 <> 0; Reorder_19;Reorder_18 <> 0; Reorder_18;Reorder_17 <> 0; Reorder_17;Reorder_16 <> 0; Reorder_16;Reorder_15 <> 0; Reorder_15;Reorder_14 <> 0; Reorder_14;Reorder_13 <> 0; Reorder_13;Reorder_12 <> 0; Reorder_12;Reorder_11 <> 0; Reorder_11;Reorder_10 <> 0; Reorder_10;Reorder_09 <> 0; Reorder_09;Reorder_08 <> 0; Reorder_08;Reorder_07 <> 0; Reorder_07;Reorder_06 <> 0; Reorder_06;Reorder_05 <> 0; Reorder_05;Reorder_04 <> 0; Reorder_04;Reorder_03 <> 0; Reorder_03;Reorder_02 <> 0; Reorder_02;Reorder_01 <> 0; Reorder_01;0)

RETURN

Final_Calculation

[akakak]

Just found a temporal solution with excel... I want it with DAX tough...! 🙂

https://docs.google.com/spreadsheets/d/1MiCwtyh5SNBHbBjYNaLvSMUV2boR7Qog/edit?usp=sharing&ouid=11793...