cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
patri0t82
Helper III
Helper III

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

1 ACCEPTED SOLUTION
RolanRodrigues
Frequent Visitor

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).
1.jpg   2.jpg   3.jpg
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)
4.jpg  5.jpg  6.jpg

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
7.jpg
step 6: build the visual by selecting the company name from Append table and Dates as a count from Table 1 and Table 2
8.jpg

Hope this helps you Have a Blessed day

View solution in original post

4 REPLIES 4
patri0t82
Helper III
Helper III

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

RolanRodrigues
Frequent Visitor

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).
1.jpg   2.jpg   3.jpg
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)
4.jpg  5.jpg  6.jpg

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
7.jpg
step 6: build the visual by selecting the company name from Append table and Dates as a count from Table 1 and Table 2
8.jpg

Hope this helps you Have a Blessed day

View solution in original post

ChrisMendoza
Super User I
Super User I

You can create a 3rd table using DAX as:

image.png

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

image.png






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



gauravtanwar
Resolver II
Resolver II

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.