cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
akakak
Regular Visitor

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

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:

akakak_3-1665164036045.png

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: 

akakak_1-1665217812191.png

akakak_2-1665217947262.png

 

 

 

1 ACCEPTED SOLUTION
v-jianboli-msft
Community Support
Community Support

Hi @akakak ,

 

Please try:

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

vjianbolimsft_0-1665390174115.png

Then add index column:

vjianbolimsft_1-1665390211789.png

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:

vjianbolimsft_2-1665390292212.png

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:

vjianbolimsft_3-1665390532440.png

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.

View solution in original post

3 REPLIES 3
v-jianboli-msft
Community Support
Community Support

Hi @akakak ,

 

Please try:

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

vjianbolimsft_0-1665390174115.png

Then add index column:

vjianbolimsft_1-1665390211789.png

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:

vjianbolimsft_2-1665390292212.png

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:

vjianbolimsft_3-1665390532440.png

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.

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
Regular Visitor

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...

 

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors