Forum Discussion

donovan_smith44's avatar
donovan_smith44
Frequent Visitor
6 years ago
Solved

Finding Common Values Between Two Columns

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.

Need Help
  • parry2k's avatar
    parry2k
    6 years ago

    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. 

7 Replies

Replies have been turned off for this discussion
  • jthomson's avatar
    jthomson
    Solution Sage
    6 years ago

    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?

    • parry2k's avatar
      parry2k
      Super User
      6 years ago

      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.

    • donovan_smith44's avatar
      donovan_smith44
      Frequent Visitor
      6 years ago

      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