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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.