Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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.

 

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

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    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)

     

2 Replies