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
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
SELECT [CaseReference] FROM Table2.2
) AND T4.[POProcess] = 1 AND T4.[PostingStatusId] = '6'