Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
I have since managed to solve the problem.
I was using || instead of &&
@Anonymous ,
Are you able to share a sanitized version of your pbix file? would really help solving the problem
Proud to be a Super User!
I have since managed to solve the problem.
I was using || instead of &&
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |