Forum Discussion

parry2k's avatar
parry2k
Super User
8 years ago
Solved

DAX for SQL Subquery with IN Operator

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...
Need Help
  • OwenAuger's avatar
    OwenAuger
    8 years ago

    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 

OwenAuger's avatar
OwenAuger
Super User
8 years ago

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's avatar
Zubair_Muhammad
Community Champion
8 years ago

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's avatar
    parry2k
    Super User
    8 years ago

    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
        )
    )