Comparing Tables

DAX DAX Calculations
1

I have a 2 pronged question:

  1. I have two lists: Table (A) has about a million rows of unique data about companies (including company names); Table (B) has about 5000 rows of unique company names. What is the best way to query table a (company name column) to determine if the companies from table B appear on that list?

  2. In some cases the company name may not be an exact match. for example, in table a the company name may appear as “The XYZ Company”, however in table b the same company may show up as “XYZ Company”. Is there a CONTAINS or NOT EXACT MATCH code that will allow me to search Table A with no exact match values from table B?

I am sure there are several ways to do this… I appreciate all suggestions… Thx!

2

Hi @pjones,

My question would be what table has the most update information concerning company names. if table A is the most up to date, why use table B. If table A have duplicate information then delete duplicated entries.

thanks Keith

3

Hi Keith, thank you for your question. To add context… these are customer lists from two different companies. Table A represents one company’s customer list; Table B represents the other company’s customer list. We are running an analysis to determine how many common customers the two companies have.

Hope that helps… Thanks again!

4

@pjones,

Very much achievable, one option to looks into columns from example to clean and standardize the Table B (company name column to match that of Table A)

Once done, then you can merge (left join) to see what are the common rows

Hope this helps.

Ansh

5

@pjones,

Is it also possible to give the sample file so I can perform those transformation for you get an idea which ones and how I would approach it.

Regards

6

@pjones,

Lots of different ways to do this. I like @AnshP’s “clean and join” strategy. Another option after you’ve cleaned is to create the following DAX table:

Common Members =

    INTERSECT(
         VALUES (name list A),
         VALUES (name list B)
    )

– Brian

1 Like
7

Hi @pjones, did the response provided by the users and experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

8

Hi @pjones, we’ve noticed that no response has been received from you since the 9th of February. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

9

A response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!