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.

Highlighted
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

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 378 members 3,324 guests
Please welcome our newest community members: