topic Re: Left anti-join in DAX in DAX Commands and Tips

Left anti-join in DAX

Anonymous — Mon, 07 Sep 2020 22:59:20 GMT

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

Re: Left anti-join in DAX

Greg_Deckler — Mon, 07 Sep 2020 23:14:12 GMT

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)

Re: Left anti-join in DAX

amitchandak — Tue, 08 Sep 2020 00:58:21 GMT

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