Find columns from one table in another table

[Ricardo77]

Hello,

i'm trying to find columns of 1 table on a 2nd table, but i'm struggling because i can't seem to use distinct or values with text operators (2nd column can be slightly different, as you'll see), and i also was trying to use addcolumns and summarize in a variable but i don't know how to use it after on the Calculate's Filter.

So what i pretend is to count table1\column1 in table2\column1, the detail is that :

- it could not exist, as AA-00004

- it can exist exactly equal or with a variation on the string (AA-00001 with AA-00001 and AA-00001--1)

What's the best / correct way to do this?
Thanks in advance

[Ricardo77]

Thanks for your help @tamerj1 , it seems OK in my real PBIx, by some reason in the PBI POC i created, with the 2 tables directly created in PBI, i was getting that message error.

[Anonymous]

i was trying now to do something more similar to you, as the idea is just to count once, but:
1) shouldn't the 1st SUMX be done over Table1, as we want to count from there?

2) can't i simply create a measure with the code you kindly shared and drag it to the screen? An error is appearing.

Thanks and Regards

[Anonymous]

Hello @tamerj1 ,

i was trying now to do something more similar to you, as the idea is just to count once, but:
1) shouldn't the 1st SUMX be done over Table1, as we want to count from there?

2) can't i simply create a measure with the code you kindly shared and drag it to the screen? An error is appearing.

Thanks and Regards

[tamerj1]

The first Values is table1 the 2nd values is table2. Please copy the same code in my last reply

[tamerj1]

Hi @Ricardo77 

you can try

=
SUMX (
    VALUES ( table2[Column1] ),
    SUMX (
        VALUES ( table1[Column1] ),
        IF ( CONTAINSSTRING ( table2[Column1], table1[Column1] ), 1 )
    )
)

[Anonymous]

Hello @tamerj1  , thanks for your help.

I think it's working but just failing in one detail: i need it to count on table1, so when values are found they just are counted once , as AA-00001 and AA-00005 .

[tamerj1]

Please clarify further

[Anonymous]

If you look to the codes in my example image it should return total of 4 from 1st table, as they are appearing 7 times, including variations, on 2nd table (forgot to place AA-00005 in blue, also).

So each entry that appears 1 or more times in 2nd table should should just count as 1.

Regards

[tamerj1 · Accepted solution]

@Anonymous 

Then use

=
SUMX (
    VALUES ( table1[Column1] ),
    IF (
        SUMX (
            VALUES ( table2[Column1] ),
            IF ( CONTAINSSTRING ( table2[Column1], table1[Column1] ), 1 )
        ) > 0,
        1
    )
)