SQL to DAX: Subquery in where statement

[Tomnibus]

I have to pull a subset of cancelled transactions from a transaction detail table.  Most transactions will wipe out additional lines in the table so my reports work fine. However, five of the transactions didn't. We haven't figured out why. But I now have to filter those out. So, I have a query like this on the back end to be able to only find the properly cancelled items:

select distinct ID_OrderNumber from Transactions as RT where ID_Activity = 377 and ID_PersonType = 584 and Date_ResAct >= convert(datetime,'2020-09-01 00:00:00.000')
and RT.ID_OrderNumber not in (Select Distinct ID_OrderNumber from Transactions as RT2 
where RT2.ID_OrderNumber = RT.ID_OrderNumber and RT2.ID_PersonType in (576,578,579,580))

I can't figure out how to do this in DAX. I did create a table that has all canceled transactions here:

AllCancels = FILTER(Transactions, Transactions[ID_Activity]=377 && Transactions[ID_PersonType]=584)

Now I need to filter the "transactions" table using the orderID's from "allcancels" to determine the bad ones and pull them out.

How do I do this?

[wdx223_Daniel]

=VAR _tbl=calculatetable(values(Transactions[ID_OrderNumber]),not(Transactions[ID_PersonType] IN {576,578,579,580})) return calculatetable(Transactions,_tbl,Transactions[ID_Activity]=377, Transactions[ID_PersonType]=584,Transactions[Date_ResAct]>=Date(2020,9,1))

[Tomnibus]

So, there is a lot of data in the tables but I have paired it down to the relevant information. I previously tried to do this with tables and HTML and ran into all sorts of problems posting so I am just doing CSV type information:

Bad Order that has extra data:

ID_OrderNumber, ID_Activity, ID_PersonType, QTY

1371455,1986,578,3

1371455,1986,578,-3

1371455,1994,580,1

1371455,1994,580,-1

1371455,377,584,1

1371455,234,114,1

Bad Order that has extra data:

ID_OrderNumber, ID_Activity, ID_PersonType, QTY

1372669,377,584,1

1372669,234,114,1

So, you notice that the bad orders have the lines with the activities in there (quantity 3 and 1) and then two other lines for reversing them. with a -3 and -1.  But all the other orders have the lines removed from the table and they only have the 377 and 234 which are cancellation lines and processing fee lines.

The only way I know to filter them out is to first gather all the orders with 377 (cancellation) and then pull out those that are in that group that also have the person types of actual orders too.  Hence my query/subquery but I don't know how to do that with DAX.

[Tomnibus]

I'm sorry, I tried to post data and the error kept coming up that I had incorrect HTML and it needed to be corrected. I will try again today.

[dedelman_clng]

Hi @Tomnibus - can you provide sample data? You should be able to do this using EXCEPT, but I would need to know what the data looks like to give the proper syntax.

David