Forum Discussion
Comparing two tables, results return from only one
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
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
4 Replies
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
You can create a 3rd table using DAX as:
then use this [Company] in your visual. Model will look something like:
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 dayThank you all for the great support and suggestions. Really appreciate it!
