DAX for SQL Subquery with IN Operator

[parry2k]

Hello fellow datanuts (@Phil_Seamark, @Vvelarde, @Greg_Deckler, @Zubair_Muhammad) Quick question for you guys:

Just wondering how you will solve following SQL query in DAX, basically sum the quantity only in case where a sales order contains productcode = '1' and same sales order contain product code '999' .

Keep in mind sales table contains millions of rows, so performance is important. 

Select 
    SUM(sales.qty) 
From sales
Join product on product.productid = sales.id
   and product.productcode = '1' 
   and sales.id IN
       (select sales.id
                 From sales 
                Join product.productid = sales.id
                          and product.product_code = '999'
      )

[OwenAuger · Accepted solution]

@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 

[Zubair_Muhammad]

HI @parry2k

@OwenAugersolution looks great..

This might work as well. Give this a shot as well
Basically it cross filters the Product Table for each Sales order to check if there are 2 specified product codes or not

Measure =
CALCULATE (
    SUM ( Sales[qty] ),
    FILTER (
        ALL ( Sales[orderid] ),
        CALCULATE (
            DISTINCTCOUNT ( 'product'[product_code] ),
            TREATAS ( { 1, 999 }, 'product'[product_code] ),
            CROSSFILTER ( Sales[id], 'product'[productid], BOTH )
        )
            = 2
    )
)

[parry2k]

Thanks @OwenAuger @Zubair_Muhammad

I have to tweak @OwenAuger measure like below:

Product Code 1 Sold = 
CALCULATE(SUM(Sale[Qty]), Filter(Product, ProductCode = 1))

Product Code 1 and 999 Sold = 
CALCUALTE([Product Code 1 Sold],
        CALCULATETABLE (
            SUMMARIZE (Sale, Sale[Order_Id] ),
            Product[ProductCode] = 999
        )
    )

[parry2k]

BTW this is what I did so far

- created a calculated column from sales using summarize and filter on product

- established relationship with calculated tables with sales table

- added calculated column in saales table to flag if invoice exists in calculate column and use this to filter records in my measure

Thanks,

P