Change a SQL 'NOT IN' into DAX

[Anonymous]

Hi,

I have 4 tables: Table1.1; Table1.2; Table2.1; Table 2.2 displaying data, all with the same columns. I have appended them all into a new master table, Table0. All the relationships in my model are working fine, I just need some DAX help.

I want to select all the values from Table1.1 and Table1.2 where the [CaseReference] does not appear in Table 2.1 or Table2.2. It isn't as simple as just selecting values from Table1.1 and Table1.2 as there are values from Table2.1 and Table2.2 in each of the other tables. I have a simplified version of the SQL code below which returns the correct figures, but I can't seem to get the correct DAX code (what I currently have is below the SQL code).

SELECT * FROM
(
    SELECT [CaseReference] FROM Table1.1 
    UNION 
    SELECT [CaseReference] FROM Table1.2
) T1
INNER JOIN Table3 T3 ON T3.[CaseReference] = T1.[CaseReference]
INNER JOIN Table4 T4 ON T4.[DocumentId] = T3.[DocumentId]
WHERE T1.[CaseReference] NOT IN 
(
    SELECT T2.[CaseReference] FROM
        (
            SELECT [CaseReference] FROM Table2.1
            UNION 
            SELECT [CaseReference] FROM Table2.2
        ) T2
)
AND T4.[POProcess] = 1 AND T4.[PostingStatusId] = '6'

Measure = 
CALCULATE(
    COUNTA(Table4[DocumentId]),
    FILTER(
        Table4,
        Table4[POProcess] = TRUE ||
        Table4[PostingStatusId] = "6"
    ),
    FILTER(
        Table0,
        CONTAINS(
            UNION(Table2.1, Table2.2),
            Table2.1[CaseReference],
            Table0[CaseReference],
            Table2.2[CaseReference],
            Table0[CaseReference]
        ) = FALSE()
    )
)

Any help is greatly appreciated!

Thanks in advance.

[richbenmintz]

@Anonymous ,

Are you able to share a sanitized version of your pbix file? would really help solving the problem

[Anonymous · Accepted solution]

I have since managed to solve the problem.

I was using || instead of &&