Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi all,
I have a table that updates weekly(every Friday). Each week there are new entries in the table. The aim is to get all the new /changed rows in the current week compared to the previous week. Each week I do it in Power query using the left anti-join option in the Merge option and send the excel sheet manually. But I was searching for an option if I can use DAX and make a table visualisation where the user can just go and export the table data. So that it's self-served and I don't have to manually do it each week.
There is a function in DAX - EXCEPT which acts as anti-join. I know how to make it work if I have 2 separate tables but in my case, I have a single table.
This is what I do. I create two tables for CompanyA with the previous week data and current week data and use left anti-join to get all the rows that are different from the previous week. The same way I do for company B.
The output of this merge query, is the final output.
Sample data-
Id | Description | Type Desc | Channel Desc | Company | Customer Id | Agent | Date |
30 | Later | Operational | Contact Centre | A | 123 | 883 | 28-Aug-20 |
29 | Later | Operational | Contact Centre | A | 456 | 883 | 28-Aug-20 |
29 | Later | Operational | Contact Centre | A | 1234 | 884 | 28-Aug-20 |
32 | Refused | Marketing | Contact Centre | B | 987 | 889 | 04-Sep-20 |
32 | Refused | Marketing | Contact Centre | B | 654 | 890 | 04-Sep-20 |
32 | Refused | Marketing | Contact Centre | B | 321 | 891 | 04-Sep-20 |
32 | Refused | Marketing | Contact Centre | B | 1718 | 134 | 04-Sep-20 |
32 | Refused | Marketing | Contact Centre | B | 1314 | 431 | 04-Sep-20 |
30 | Later | Operational | Contact Centre | A | 123 | 883 | 04-Sep-20 |
29 | Later | Operational | Contact Centre | A | 456 | 883 | 04-Sep-20 |
29 | Accept | Operational | Contact Centre | A | 1234 | 884 | 04-Sep-20 |
31 | Later | Operational | Contact Centre | A | 789 | 885 | 04-Sep-20 |
32 | Accept | Operational | Contact Centre | A | 1011 | 886 | 04-Sep-20 |
33 | Later | Operational | Contact Centre | A | 1213 | 887 | 04-Sep-20 |
32 | Refused | Marketing | Contact Centre | B | 987 | 889 | 28-Aug-20 |
32 | Refused | Marketing | Contact Centre | B | 654 | 890 | 28-Aug-20 |
32 | Refused | Marketing | Contact Centre | B | 321 | 891 | 28-Aug-20 |
12 | Refused | Marketing | Contact Centre | B | 1718 | 133 | 28-Aug-20 |
10 | Refused | Marketing | Contact Centre | B | 1314 | 631 | 28-Aug-20 |
I have tried to explain it in detail. Please let me know if something is not clear. It would be great if anyone could help.
Best regards,
Supriya
Solved! Go to Solution.
@Anonymous Just create two separate tables in VAR's:
New Table =
VAR __Table1 = FILTER('Table',<filters go here>)
VAR __Table2 = FILTER('Table',<different filters go here>)
RETURN
EXCEPT(__Table1,__Table2)
If Power Query, create a base table with all of your records. Right-click, create a reference, filter. Right-click base table again, filter differently. Create a Merge query and merge your 2 filtered tables together. Go back and disable load on any tables you don't want in your model (right-click)
@Anonymous , See if they way of incremental can help
https://blog.crossjoin.co.uk/2020/04/13/keep-the-existing-data-in-your-power-bi-dataset-and-add-new-data-to-it-using-incremental-refresh/
https://www.thebiccountant.com/2017/01/11/incremental-load-in-powerbi-using-dax-union/
In DAX, Data In Table1, not in Table 2
New Table = except(Table1,Table2)
https://docs.microsoft.com/en-us/dax/except-function-dax
Combine only new data with old data
Union('Old data',except('New date','Old Date'))
@Anonymous Just create two separate tables in VAR's:
New Table =
VAR __Table1 = FILTER('Table',<filters go here>)
VAR __Table2 = FILTER('Table',<different filters go here>)
RETURN
EXCEPT(__Table1,__Table2)
If Power Query, create a base table with all of your records. Right-click, create a reference, filter. Right-click base table again, filter differently. Create a Merge query and merge your 2 filtered tables together. Go back and disable load on any tables you don't want in your model (right-click)
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
24 | |
9 | |
7 | |
6 | |
6 |
User | Count |
---|---|
29 | |
11 | |
11 | |
10 | |
6 |