Check if a value or it`s substrings in one table have an equivalent in another table

[ardisson]

Hello guys, 

I'm stuck with the task about to be explained, and really hope you guys can help me.

I have 2 tables, A and B, the first of them has a column W with binary(base 2) values (around 100 lines), and the second has a column Z with around 10k binary(base 2) values. I have to check if each value from table A or it`s substrings has an equivalent in the column in table B. 

As an example, considering that the first value from column W table A is "110001011101100000000001", i have to check if this 24 digit binary has an exact equivalent in column Z in table B, and in that case, get the value in column X from table B and return it to another column in table A. If not, I have to proceed checking if the first 23 digit's substring has the equivalent, getting the Column X value if true, proceding to check the first 22 digits if not, and so on until I get an exact match or until only 12 digits remain.

I really apreciate any help you guys could give me.

Thank you!

[tamerj1]

Hi @ardisson 
Please refer to attached file with the solution

Code = 
MAXX (
    FILTER (
        TableB, 
        CONTAINSSTRING ( TableA[Binary], TableB[Binary] )
    ),
    TableB[Codes]
)

[ardisson]

Hi @tamerj1!

I tried the solution you presented, and at first it seemd it would work, however, I found a problem.

The CONTAINSSTRING check a value along the entire body of another value, and in my case, a need this checking to start exactly at position 0 of the compared value. As an example 110110110011001100 has binary 110011001100 (starting at position 6) in it, but it doesn`t start at position 0. So it would return a false positive.

In my case, the binary value to be compared from table B must always start the comparison at position 0 of the value from table A, and if it didn`t find anything, it must check if the substring of the binary from table A (LEN[X] - 1) is equal to any binary in table B, also starting at position 0.

So, 110110110011001100 from table A must not bring the Code value from the binary 110011001100 in table B, because 110110110011001100 is not equal to 110011001100, and 11011011001100110 neither, and 1101101100110011 neither, and so on until only 12 digits are left, As a result, It would not return any value.

But it would bring a Code value from
110110110011 in Table B, because 110110110011 is equal to LEFT(110110110011001100, LEN(110110110011001100) - 6), and has 12 digits or more.

If DAX had something like STARTSWITH, I could replace the CONTAINSSTRING with it...

Have you any idea how could I manage to get that?

Thanks @tamerj1 !

[tamerj1]

@ardisson 

I'm not on my PC so I did't test. Please try

Code =
MAXX (
    FILTER (
        TableB,
        LEFT ( TableA[Binary], LEN ( TableB[Binary] ) ) = TableB[Binary]
    ),
    TableB[Codes]
)

[ardisson]

Hi @tamerj1,

I`ve tried that before. For some reason, it only brings me the same value from table B for all the lines in table A, which I know is not correct.

Thanks!

[tamerj1 · Accepted solution]

@ardisson 
Seems to be working in the sample file

[ardisson]

Hi @tamerj1 !

Indeed, it was working. My Binaries where presenting an error. I did correct them, and then it worked perfectly.

Thank you so much for the help!

[tamerj1]

Hi @ardisson 

please present a sample data along with the expected results 

[ardisson]

Hi @tamerj1, thank you for the reply. Let me see if I can illustrate it a little:

Table A
Binary                                             Codes
110100100001010100110100
111100010010110101
101100110100110000110
110100100001010100110100
111100010010110101

Table B
Binary                                           Codes
110100100001010100110            ABC
1011001101001100001                DEF
1111000100101101                      GHI

Table A (Result)

Binary                                           Codes

110100100001010100110100      ABC

111100010010110101                  GHI

101100110100110000110            DEF

110100100001010100110100      ABC

111100010010110101                  GHI

As you can see, I have to bring to Table A the unique Codes from Table B by checking if each Binary from Table A or their substrings are equal to any Binary in Table B.

In the example above, the first item in Binary column from Table A, 110100100001010100110100, doesn't have a equivalent in Binary in Table B, so i have to check it's substring (something like LEFT(TableA[Binary], Len(TableA[Binary]) - 1)) to see if I get an equivalent in Table B. If not, I must try the next substring LEFT(TableA[Binary], Len(TableA[Binary]) - 2), and so on, until I find an equivalent in Table B (in this case, 110100100001010100110, or LEFT(TableA[Binary], Len(TableA[Binary]) - 3)), then bring to Codes column in Table A the Codes value (ABC) of the equivalent in Table B.

Thanks again!