DAX Formula (NOT IN)

[ernolan]

This should be very simple. It certainly is in standard SQL. I have yet to find a way to do this with DAX. Simply put, I’m looking for a way to grab all of the records that don’t have a corresponding value in another table. For the SQL Gurus, this is simply done with a NOT IN clause.

Perhaps the illustration will help. The ATable has the one-to-many relationship with the BTable. For every primary key in the ATable, there maybe no values or a list of values in the BTable.

Let’s say the I want all of the records in the ATable that don’t have an Action Code value of “2” in the BTable. Based on the illustration below, I would get records 1001 and 1003. Records 1002 and 1004 have a value of “2” so I’m not interested in them.

Please and thanks in advance for any ideas you have. Cheers.


ATable BTable
RFSID RFSID Action Code
1001 1001 1
1002 1001 3
1003 1002 1
1004 1002 2
1002 3
1003 1
1003 3
1004 1
1004 2
1004 3

 

[gazpage]

Can think of a few ways to do this, I can't quite follow your data structure to write the exact code:

1 Use the EXCEPT formula. If you first filter table A so that it includes the items that DO match, and then use that as the second table expression in the EXCEPT. Pseudo code is something like:

EXCEPT (
    TableA,
    CALCULATETABLE (
        TableA,
        FILTER (
            TableB,
            TableB[Col] = 2
        )
    )
)

2 Use the CONTAINS formula with a NOT. Again Pseudocode

FILTER (
    TableA,
    NOT ( CONTAINS ( 
        TableB,
        TableB[Col],
        2)
    )
)
)

Hope this is more helpful than nothing.