Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors