DAX LeftAnti

[cnestg8r]

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.

 

[anvg]

Hi
Try with https://msdn.microsoft.com/en-us/library/dn802527.aspx
Regards,

 

[cnestg8r]

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

 

[Ozeroth]

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:

=
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:

=
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]

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]

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]

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.