Compare two tables and display missing rows from the first table in a dynamic fashion

[Anonymous]

Hi Folks,

I am a novice in power BI. I have two different tables that have hundreds of rows in them. The first table has live data i.e data changes every few seconds. The data in the second table changes once a day. These two tables are connected by a common column(Name). I want to compare two tables on the column 'name' and then display the rows of table-1 which are not present in Table-2. This comparison needs to be done dynamically(Since the Table-1 has live data). Can anyone help me how to do this:
My data looks like this

TABLE - 1

   Name      Occupation     Address     Age

John	Artist	Houston	40
Adam	Teacher	Dallas	42
Nikky	Driver	Arlington	35
Roy	Engineer	Austin	33
Miller	Waiter	Dallas	41
Kieren	Businessman	Palo	29
Morkel	Athlete	Alto	44
Warner	Coach	San Jose	22
Bairstow	Administrator	Cleveland	66

TABLE - 2

Name   Marital Status     Phone

Adam	Married	2341232345
Roy	Single	7781231223
Miller	MArried	4561947563
Morkel	Single	3864977155

DESIRED OUTPUT

Name          Occupation     Address    Age

John	Artist	Houston	40
Nikky	Driver	Arlington	35
Kieren	Businessman	Palo	29
Warner	Coach	San Jose	22
Bairstow	Administrator	Cleveland	66

[Fowmy · Accepted solution]

@Anonymous 

Create a measure to check if the records exist in Table 2

ExistInTable2 =  CONTAINS('TABLE - 2','TABLE - 2'[Name],MAX('TABLE - 1'[Name])) * 1

Add the above measure to the Visual Filter of Table 1 and set to  = 0 and click apply. It will update automatically 

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[PaulDBrown · Accepted solution]

@Anonymous 

One way is to create a measure to use in the filter pane:

Names Exclusive To table 1 = 
VAR Tab1 = VALUES(Table1[Name])
VAR TAb2 = VALUES(Table2[Name])
RETURN
COUNTROWS(EXCEPT(Tab1, TAb2))

Add this measure to "Filters for this visual"  in the filter pane and set the value to 1

[al1981]

Dear @PaulDBrown many tnx for this solution! I found it really usefull for me!!!

[Icey]

Hi @Anonymous ,

Please let us know whether @Fowmy 's method is helpful.

If it is, please accept his reply as solution to your question so that people who may have the same question can get the solution directly.

If not, please give us more details.

Best Regards,

Icey

[Fowmy · Accepted solution]

@Anonymous 

Create a measure to check if the records exist in Table 2

ExistInTable2 =  CONTAINS('TABLE - 2','TABLE - 2'[Name],MAX('TABLE - 1'[Name])) * 1

Add the above measure to the Visual Filter of Table 1 and set to  = 0 and click apply. It will update automatically 

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[amitchandak]

@Anonymous , Please find the attached file after the signature. the new table is create.

Measure like this

Measure = var _new = EXCEPT(ALL(Table1[Name]),all(Table2[Name])) return if( max(Table1[Name]) in _new,1,blank())

[Anonymous]

Hi @amitchandak,

The table you have created is working well when the data is stable in two tables. The measure, you have created displays the value 1. As the data in table-1 changes every few seconds, the table you have created is not considering the new rows that are being updated. We have to continuously compare the two tables and update the new table continuously. Do you have any kind of solution to this problem?