Forum Discussion
check if a value in table 1 exist in table2
Hi Anonymous
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.
Community ChampionHi Anonymous
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.
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.
- AlB
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:
Cheers