topic Re: Find columns from one table in another table in DAX Commands and Tips

Find columns from one table in another table

Ricardo77 — Tue, 14 Jun 2022 13:40:41 GMT

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

Re: Find columns from one table in another table

tamerj1 — Tue, 14 Jun 2022 17:28:19 GMT

Hi Ricardo77

you can try

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

Re: Find columns from one table in another table

Anonymous — Tue, 14 Jun 2022 17:42:54 GMT

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 .

Re: Find columns from one table in another table

tamerj1 — Tue, 14 Jun 2022 18:05:47 GMT

Please clarify further

Re: Find columns from one table in another table

Anonymous — Tue, 14 Jun 2022 18:13:37 GMT

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

Re: Find columns from one table in another table

tamerj1 — Tue, 14 Jun 2022 18:43:58 GMT

Anonymous

Then use

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

Re: Find columns from one table in another table

Anonymous — Tue, 14 Jun 2022 18:50:30 GMT

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

Re: Find columns from one table in another table

tamerj1 — Tue, 14 Jun 2022 19:06:59 GMT

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

Re: Find columns from one table in another table

Anonymous — Tue, 14 Jun 2022 19:49:26 GMT

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

Re: Find columns from one table in another table

Ricardo77 — Tue, 14 Jun 2022 23:08:35 GMT

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.