cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
alepage Member
Member

check if a value in table 1 exist in table2

Hello,

 

I have two tables.  Table1 contains let's say 15,500,000 rows and table2 contains 15,263,123 rows.

 

In table 1, I have the field UniqueKey1 and in Table 2 I have the field UniqueKey2.

 

In principle, all the UniqueKey2 values should be found in UniqueKey1.  I want to set a flag1 to check if the UniqueKey1 exist in UniqueKey2. 

 


As my two tables are huges, I limited by the memory issue of power bi. 

What's the best way to do that?

 

Thanks in advance for your help

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: check if a value in table 1 exist in table2

Hi @alepage 

 

I would created a calculated column:

 

 

NewColumn =
IF ( Table1[UniqueKey1] IN DISTINCT ( Table2[UniqueKey2] ), 1, 0 )

Since the large majority of NewColumn values will be 1, the engine will be able to compress that NewColumn a lot and thus it won't take up too much additional space.

3 REPLIES 3
Super User
Super User

Re: check if a value in table 1 exist in table2

Hi @alepage 

 

I would created a calculated column:

 

 

NewColumn =
IF ( Table1[UniqueKey1] IN DISTINCT ( Table2[UniqueKey2] ), 1, 0 )

Since the large majority of NewColumn values will be 1, the engine will be able to compress that NewColumn a lot and thus it won't take up too much additional space.

alepage Member
Member

Re: check if a value in table 1 exist in table2

Hello,

 

I have test your solution it works perfectly.  However, as I am new Power BI user, could you please explain me how this code works because I have never use distinct in?

 

Thanks for your help.

Super User
Super User

Re: check if a value in table 1 exist in table2

Sure.

DISTINCT gives you all the distinct  values in the column, in this case all UniqueKey 2.

Then, for each row in the first table, we check whether that UniqueKey1 is in the list of Unique Keys 2,. If so, we return a 1; otherwise a 0. If you find this helpful, please consider kudoing the posts. Check these out:

https://dax.guide/op/in/

https://dax.guide/distinct/ 

 

Cheers