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

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 Solution Sage
Solution Sage

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 Helper IV
Helper IV

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?

Highlighted
thmonte Helper IV
Helper IV

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
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors