Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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' )
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Solved! Go to Solution.
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
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
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 ) )
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 ) )
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
95 | |
77 | |
65 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |