DAX LeftAnti

cnestg8r

Active Member
Joined
Dec 26, 2005
Messages
284
In order to find the rows of table A that are NOT in Table B, one can use M in a query.

e.g., Table.NestedJoin(Employee,{"EmpID"},#"Employee Salary",{"EmployeeId"},"Employee Salary",JoinKind.LeftAnti)

How can it be done in DAX, perhaps using GENERATE. GENERATE or GENERATEALL makes it easy to create inner, outer, and cross joins, but not ANTI as far as I know.

Many thanks.
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

cnestg8r

Active Member
Joined
Dec 26, 2005
Messages
284
Hi,

NaturalLeftOuterJoin returns all rows from Table A including matched and unmatched rows from Table B. I only want the rows from A that do not have a B matching key. In the M code of Query, that is called LeftAnti.

Regards
 
Last edited:

Ozeroth

Active Member
Joined
Dec 14, 2013
Messages
264
Hi there cnestg8r,

My suggestion would be to use CALCULATETABLE with EXCEPT to apply a filter on EmpID that excludes values present in the Employee Salary table.

For example:
Code:
=
CALCULATETABLE (
    Employee,
    EXCEPT (
        VALUES ( Employee[EmpID] ),
        VALUES ( 'Employee Salary'[EmployeeId] )
    )
)

The above formula would take into account the filter context in which it is called.

Alternatively, a version that ignores any filters might be something like:
Code:
=
CALCULATETABLE (
    Employee,
    EXCEPT (
        ALL ( Employee[EmpID] ),
        ALL ( 'Employee Salary'[EmployeeId] )
    ),
    ALL ( Employee )
)

Also I've assumed no relationships between the tables mentioned.

There may be other ways to achieve your end result (and the DAX may be different) if the tables are related.

Where are you using this table (e.g. within a measure or as a calculated table) and what are you using it for?

Regards,
Owen
 

cnestg8r

Active Member
Joined
Dec 26, 2005
Messages
284

ADVERTISEMENT

Hi Owen,
Thank you for your sage advice. I'll give them a try.

This is an odd situation where the tables are related 1:1 in theory. However rows in A are not in B, and vice versa. Imagine key A {1,2,3,4,5} and key B {3,4,5,6}. In reality the proper result would have maybe 100k rows in each, and could easily be merged. Identifying the exceptions should lead to a cleaner database.

My biggest frustration in this data "science" is the inaccuracies of raw data. This has been true across multiple clients.

I welcome your feedback and advice.
Thanks
 

Ozeroth

Active Member
Joined
Dec 14, 2013
Messages
264
No problem :)
From what you've described, you're really cleaning up your source data so I would say Power Query is a better tool than DAX.

You could use Table.NestedJoin to create a Full Outer Join between the tables if each table may contain values not present in the other. Then at least you have a table with all known keys.

Then possibly add a flag column or columns indicating which table(s) had missing data.

You could have some visuals that show the status e.g. summarize unmatched values from one table or the other.

Eventually, if the data are fixed at source, these summary visuals will show no unmatched values.

Anyway, those are just a few ideas :)

Regards,
Owen
 

cnestg8r

Active Member
Joined
Dec 26, 2005
Messages
284
Thanks, Owen. I appreciate the thoughts and agree that Power Query is the easier way. Your suggested approach facilitates dynamic visuals. Using PQ LeftAnti and RightAnti would be static.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,497
Messages
5,596,508
Members
414,073
Latest member
Contilly

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top