Finding Common Values Between Two Columns

[donovan_smith44]

Hi all,

I have two columns. One with 500,000+ rows, the other with about 10,000.

I am trying to use DAX to find the common values between these columns, is there any way to do this?

Thanks!

EDIT: To explain what is required, would like to search First External Reference Number column for all of the values in the Second External Reference Number column, and then return a column with all of those values.

[jthomson]

Could make a new table that just takes a union of the distinct values of each, relate your original two tables to it, then make a measure multiplying countrows of each of the original two tables?

[donovan_smith44]

Hi,

Thanks for your response. I am very new to DAX and Power BI so forgive me if this sounds dumb... (just not sure I understand the DAX behind your description)

So i'm not sure if this will affect what you described, but many of the values in the column with 10k (call it column 1) rows will not be found in the column with 500k+ (call it column 2).  

I'd need a column that searches column 2 for all values in column 1, and return whatever is successfully found. 

Thanks again

[parry2k]

@donovan_smith44 so let's put sample data here, which is always good way to explain and get the answer

Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Assuming your data looks like this

Col1    Col2

1           5

2           6

             1

             2

So in above case 1 and 2 from col1 exists in col2 and you want to show those values, correct?

[donovan_smith44]

Hi,

Thanks for reply and sharing-- should have looked into that guide more before posting.

Yes, you are correct. 

I would like search First External Reference Number column for all of the values in the Second External Reference Number column, and then return a column with all of those values.

[parry2k · Accepted solution]

@donovan_smith44 go to modelling table and click New Table and following expression

New Table =
INTERSECT ( 
VALUES ( Table[First External Reference Number] ), 
VALUES ( Table[Second Externa Reference Number] )
)

Above expression will add another table in your mode called New Table and that will have your data. 

[donovan_smith44]

Thank you... this is exactly what I needed.

[parry2k]

@donovan_smith44 yes it is possible, how you want the output to be? It can be created as seperate table or we can write a column where value is found in both and you can filter on that, so no new table.