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! ...
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. 

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

    • parry2k's avatar
      parry2k
      Super User
      6 years ago

      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's avatar
        donovan_smith44
        Frequent Visitor
        6 years ago

        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.