topic Re: Check if a value or it`s substrings in one table have an equivalent in another table in DAX Commands and Tips

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

ardisson — Thu, 29 Sep 2022 01:36:12 GMT

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!

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

tamerj1 — Thu, 29 Sep 2022 03:05:06 GMT

Hi ardisson

please present a sample data along with the expected results

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

ardisson — Thu, 29 Sep 2022 10:17:25 GMT

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!

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

tamerj1 — Thu, 29 Sep 2022 10:29:07 GMT

Hi ardisson
Please refer to attached file with the solution

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

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

ardisson — Thu, 29 Sep 2022 13:18:06 GMT

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 !

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

tamerj1 — Thu, 29 Sep 2022 13:31:08 GMT

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] )

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

ardisson — Thu, 29 Sep 2022 13:47:58 GMT

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!

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

tamerj1 — Thu, 29 Sep 2022 15:57:10 GMT

ardisson
Seems to be working in the sample file

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

ardisson — Tue, 04 Oct 2022 01:03:43 GMT

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!