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

 

 

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 

4 Replies

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

    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