Comparison of multiple columns in two tables

[Anonymous]

 Hi All,

I am trying to comparare multiple columns between two tables (Dev and Production).

(Table 1)Production: Reference, Type, Status, Date

(Table 2)Dev: Reference, Type, Status, Date

The three main attributes I need to base my report on are:  Type, Reference, Status

I need to create a report to show the rows in table 2 that are not matching to table 1 based on the attributes Type, Reference, Status. 

Reference	Type	Status	Prod Match?
ab1	invoice	closed	Prod - No match
bb2	invoice	open	Prod - No match
cb3	credit	pending	Prod - No match
db4	online	open	Prod - No match

Please can someone advise?

@Anonymous 

[v-jingzhang · Accepted solution]

Hi @Anonymous

You can first add a new column in each table to combine three columns:

Column = [Reference] &"-"& [Type] &"-"& [Status]

Then in Dev table, add the following column to get "Match"/"No Match" results. 

Match ? = IF(Dev[Column] IN VALUES(Production[Column]), "Match", "No Match")

This is a similar thread for your reference: https://community.powerbi.com/t5/Desktop/Compare-Names-between-tables/m-p/2123547 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[amitchandak]

@Anonymous , one way is that you create common dimension or dimensions for Type, Reference, Status

Join with both tables and show count of date from both table and put visual level filter for count from table 1 is blank

Second one is new table with except

except (summarize(Table1,[Type], [Reference], [Status]),summarize(Table2,[Type], [Reference], [Status]))

[Anonymous]

Hi @amitchandak,

Thank you - I also need to show the rows of both tables 1 and 2 that don't match. Do I need to include an additional query?