Convert query to dax within table loop

To replicate the logic of your SQL query in DAX, you'll need to create a calculated table or calculated column to achieve the same result. Since DAX doesn't directly support subqueries like SQL, you'll have to think differently about how to structure your logic. One way to approach this is by creating calculated columns and measures in Power BI or Power Pivot.

Here's how you can break down your logic:

1. Count the distinct receipts where 'WATER BOTTLE 1L' is sold.
2. Filter the table to exclude 'WATER BOTTLE 1L' items.
3. Count the occurrences of each remaining item.
4. Return the top 10 items by count.

Here's how you can implement this logic in DAX:

Top10ItemsExceptWaterBottle =
VAR WaterBottleReceipts =
CALCULATETABLE (
DISTINCT ( TABLE_A[RECEIPT_NO] ),
FILTER ( TABLE_A, TABLE_A[ITEM] = "WATER BOTTLE 1L" )
)
VAR NonWaterBottleItems =
FILTER ( TABLE_A, TABLE_A[ITEM] <> "WATER BOTTLE 1L" )
VAR NonWaterBottleItemsInWaterBottleReceipts =
CALCULATETABLE (
VALUES ( NonWaterBottleItems[ITEM] ),
INTERSECT ( VALUES ( NonWaterBottleItems[RECEIPT_NO] ), WaterBottleReceipts )
)
RETURN
TOPN (
10,
SUMMARIZE (
FILTER ( NonWaterBottleItems, NonWaterBottleItems[ITEM] IN NonWaterBottleItemsInWaterBottleReceipts ),
NonWaterBottleItems[ITEM],
"Count", COUNTROWS ( NonWaterBottleItems )
),
[Count], DESC
)

Here's a breakdown of what's happening:

• WaterBottleReceipts: This variable calculates a table of distinct receipt numbers where 'WATER BOTTLE 1L' is sold.
• NonWaterBottleItems: This variable filters the table to exclude 'WATER BOTTLE 1L' items.
• NonWaterBottleItemsInWaterBottleReceipts: This variable calculates a table of non-water bottle items sold in the same receipts as 'WATER BOTTLE 1L'.
• The SUMMARIZE function groups the non-water bottle items by the item name and counts the occurrences.
• Finally, TOPN is used to select the top 10 items by count, ordered in descending order.

You can create a new measure using the above DAX expression and use it in your Power BI report or Pivot table to get the desired result. Make sure to replace TABLE_A with the name of your table in your Power BI or Power Pivot model.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.