Forum Discussion
Compare two tables and display missing rows from the first table in a dynamic fashion
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 |
Anonymous
Create a measure to check if the records exist in Table 2ExistInTable2 = CONTAINS('TABLE - 2','TABLE - 2'[Name],MAX('TABLE - 1'[Name])) * 1Add 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 🙂
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
6 Replies
- Fowmy
Super User
Anonymous
Create a measure to check if the records exist in Table 2ExistInTable2 = CONTAINS('TABLE - 2','TABLE - 2'[Name],MAX('TABLE - 1'[Name])) * 1Add 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 🙂
PaulDBrownCommunity Champion
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
al1981Helper II
Dear PaulDBrown many tnx for this solution! I found it really usefull for me!!!
- 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())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?
