Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Left anti-join in DAX

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.

pbi.JPG

 

Sample data-

 

IdDescriptionType DescChannel DescCompanyCustomer IdAgentDate
30LaterOperationalContact CentreA12388328-Aug-20
29LaterOperationalContact CentreA45688328-Aug-20
29LaterOperationalContact CentreA123488428-Aug-20
32RefusedMarketingContact CentreB98788904-Sep-20
32RefusedMarketingContact CentreB65489004-Sep-20
32RefusedMarketingContact CentreB32189104-Sep-20
32RefusedMarketingContact CentreB171813404-Sep-20
32RefusedMarketingContact CentreB131443104-Sep-20
30LaterOperationalContact CentreA12388304-Sep-20
29LaterOperationalContact CentreA45688304-Sep-20
29AcceptOperationalContact CentreA123488404-Sep-20
31LaterOperationalContact CentreA78988504-Sep-20
32AcceptOperationalContact CentreA101188604-Sep-20
33LaterOperationalContact CentreA121388704-Sep-20
32RefusedMarketingContact CentreB98788928-Aug-20
32RefusedMarketingContact CentreB65489028-Aug-20
32RefusedMarketingContact CentreB32189128-Aug-20
12RefusedMarketingContact CentreB171813328-Aug-20
10RefusedMarketingContact CentreB131463128-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

1 ACCEPTED SOLUTION
Greg_Deckler
Community Champion
Community Champion

@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)

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@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'))

https://docs.microsoft.com/en-us/dax/union-function-dax

https://docs.microsoft.com/en-us/dax/intersect-function-dax

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Greg_Deckler
Community Champion
Community Champion

@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)

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.