DAX for SQL Subquery with IN Operator

parry2k

If you're wanting a measure, something along these lines should work.

The logic in your 2nd post sounds similar to this (I was guessing a bit with column names & related tables, so replace the red as needed):

Sales For Orders Containing Both 1 and 999 =
CALCULATE (
    SUM ( Sales[Qty] ),
    CALCULATETABLE (
        SUMMARIZE ( Sales, Sales[OrderID] ),
        Product[Product_Code] = 1
    ),
    CALCULATETABLE (
        SUMMARIZE ( Sales, Sales[OrderID] ),
        Product[Product_Code] = 999
    )
)

EDIT: Actually you may get better performance nesting the CALCULATETABLES, with whichever Product is less frequent in the inner CALCULATETABLE:

Sales for Orders Containing Both 1 and 999 = 
CALCULATE (
    SUM ( Sales[Qty] ),
    CALCULATETABLE (
        SUMMARIZE ( Sales, Sales[OrderID] ),
        Product[Product_Code] = 1,
        CALCULATETABLE (
            SUMMARIZE ( Sales, Sales[OrderID] ),
            Product[Product_Code] = 999
        )
    )
)

This seems to perform quite quickly on a dummy table with 5m rows ( 10k orders and 500 products per order).

Regards,

Owen