Comparing two tables, results return from only one

[patri0t82]

Hello,

I have two separate tables Table1 and Table2 with Date and Company in each.

I am trying to compare counts of both tables in a visualization. The problem I am encountering is in my visualization. I'm showing it in three columns

Company - DateCountTable1 - DateCountTable2

When I do this, I am using 'Company' from Table2, and in my visualizations, this shows all the companies from Table2 and matching companies from Table1 - I am missing additional unique companies from Table1.

Vice versa, I could use 'Company' from Table1, but then I'd only see companies from Table1 and Companies from Table2 that match, and not any additional Companies only found in Table2.

This seems like a simple issue, but it's been rather difficult coming up with a solution!

Thanks

[patri0t82]

Thank you all for the great support and suggestions. Really appreciate it!

[RolanRodrigues · Accepted solution]

Step 1: You will need to create a reference to Table1 in query editor  keep only the company column in it and remove duplicates (company name).
      
Step 2: You will need to create a reference to Table2 in query editor  keep only the company column in it and remove duplicates (company name).

Step 3: Append the two referenced tables using 'Append Queries' and remove duplicates (company names)
    

Step 4: close and apply

Step 5: you need to now set relationship between Table 1 and Table 2 company name to the Appended Company name

step 6: build the visual by selecting the company name from Append table and Dates as a count from Table 1 and Table 2


Hope this helps you Have a Blessed day

[ChrisMendoza]

You can create a 3rd table using DAX as:

then use this [Company] in your visual. Model will look something like:

[Anonymous]

This is happening because you don't have a table which has all the company names from table 1 and 2. If you are using import mode, I suggest you go to the query editor and use 'append queries as new' option. This will create a third table which you can use to pull data in your 'company' column. The idea is to have a table which contains all company names. Let me know if you have any doubts