cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Change a SQL 'NOT IN' into DAX

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Anonymous
Not applicable

I have since managed to solve the problem.

 

I was using || instead of &&

View solution in original post

2 REPLIES 2
Solution Sage
Solution Sage

@Anonymous ,

 

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

 

Anonymous
Not applicable

I have since managed to solve the problem.

 

I was using || instead of &&

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors