cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
thmonte Member
Member

Comparing columns from two different tables that have relationships and show differences only

So I am looking for some assistance creating a discrepancy report.  I have two databases that should always be in sync with each other and I am going to be using PowerBI to spot check this.  The databases are not completely matching but with some reference data I was able to create relationships to get what needs to be check into one visual.  The end goal is to have one visual that disables all related rows that are out of sync with each other and possibly highlight the differences.

 

 

So what the visual looks like now is this, there is one more column that is not showing here that makes each of these rows unique.  What I am looking to do is compare A1 to B1 and if they do not equal each other show the differences.  I am assuming I can create a new table that stores only the differences and show the entire table in a visual?  I've done some searching and I've seen recommendations on using DAX RELATED but I can't seem to get that working.  Remember I have created reference data that is used to tie the two tables together.  Any help is greatly appreciated!  Thanks.

 

 

table_visual.jpg

3 REPLIES 3
CahabaData New Contributor
New Contributor

Re: Comparing columns from two different tables that have relationships and show differences only

please clarify: you state & display a visual where both A1 & B1 are together already.  

 

so you do have the 2 tables joined it appears

 

if this is the case you can create a measure 

Different Flag = if (A1 <> B1, 1, 0)

 

this measure should work in the same visual, putting a 1 where they differ.  One could then apply a page level filter of just 1s for this field/column

 

 

 

 

www.CahabaData.com
thmonte Member
Member

Re: Comparing columns from two different tables that have relationships and show differences only

Thats a pretty interesting solution and it might work.  I was able to get it all in the same visual by creating relationships between the two tables using reference data.  How can I create the measure to pull in the value across two seperate tables?

thmonte Member
Member

Re: Comparing columns from two different tables that have relationships and show differences only

@CahabaData I'm having a hard time getting the proper formula to work

 

VAR __BASELINE_VALUE = COUNTA(table1[A1])
VAR __VALUE_TO_COMPARE = COUNTA(table2[B1])
RETURN
	IF(__BASELINE_VALUE <> __VALUE_TO_COMPARE,"T","F")

This is what I tried but it is not returning the right results.  It looks like its adding the numbers.

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors