DAX LeftAnti

cnestg8r

Active Member
Joined
Dec 26, 2005
Messages
287
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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.
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

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
Back
Top