Forum Discussion
Convert query to dax within table loop
Hi, I'm new to DAX... anyone can help me to convert this query to DAX.. i keep fail to get the result same as this query
SELECT
DISTINCT TOP(10) [ITEM],
COUNT([ITEM]) AS 'count'
FROM
[TABLE_A]
WHERE
[RECEIPT_NO] IN
(
SELECT
DISTINCT [RECEIPT_NO]
FROM
[TABLE_A]
WHERE
[ITEM] = 'WATER BOTTLE 1L'
GROUP BY
[RECEIPT_NO]
)
AND [ITEM] != 'WATER BOTTLE 1L'
GROUP BY
[ITEM]
ORDER BY
'count' DESC
12 Replies
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:
- Count the distinct receipts where 'WATER BOTTLE 1L' is sold.
- Filter the table to exclude 'WATER BOTTLE 1L' items.
- Count the occurrences of each remaining item.
- 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.
hi danny3313 ,
Another method. Please note that this is a Calculate table(Not column or measure). I have used a test table for this which I have shared below. You need to replace column name with yours. Sharing comments to help understand what this code does.
Temp =//Getting only Receipt No where Water bottle1LVAR _WB1L =SELECTCOLUMNS(SUMMARIZE(FILTER( ITEMS, ITEMS[ITEM] = "WATER BOTTLE 1L"),ITEMS[RECEIPT_NO]),"RCPTNO", [RECEIPT_NO])//Getting all records from table where Item is not Water Bottle 1L, this also includes receipt no under which no Water bottle was purchased.VAR _NOWB1L =SELECTCOLUMNS(FILTER( ITEMS, ITEMS[ITEM] <> "WATER BOTTLE 1L"),"RCPTNO", [RECEIPT_NO],"ITEMNAME", [ITEM])//Joining above to tables to remove Receipt no under which there is no Water Bottle was purchased.VAR _JOINTBL = NATURALINNERJOIN( _NOWB1L, _WB1L)//Grouping on Item and adding countVAR _SUMTBL =ADDCOLUMNS(SUMMARIZE(_JOINTBL,[ITEMNAME]),"CountItem",VAR _Item = [ITEMNAME]RETURN COUNTX( FILTER(_JOINTBL, [ITEMNAME] = _Item), [ITEMNAME]))//Returning TOP 2 items by count, you can change this number to your reuirement. I had few records to used 2.RETURN TOPN( 2, _SUMTBL, [CountItem],DESC)Source TableITEMRECEIPT_NOWATER BOTTLE 1L 1 GLASS 1 BOTTLE 1 CHOCOLATES 1 WATER BOTTLE 1L 2 PHONE 2 LAPTOP 2 CHARGER 2 ICECREAM 2 FRUITS 2 VEGETABLES 2 NUTS 2 VEGETABLES 3 NUTS 3 PHONE 4 LAPTOP 4 CHARGER 4 ICECREAM 4 FRUITS 4 VEGETABLES 4 VEGETABLES 5 FRUITS 5 WATER BOTTLE 1L 5 VEGETABLES 5 WATER BOTTLE 1L 5 FRUITS 5 VEGETABLES 5 FRUITS 5 NUTS 5 hi danny3313 ,
This one doesn't use Join, Again a calculated table.
Temp =VAR _SummData =ADDCOLUMNS(ITEMS,"HasBottle",VAR _ReceiptNo = ITEMS[RECEIPT_NO]RETURN CALCULATE( MAX(ITEMS[ITEM]), REMOVEFILTERS(ITEMS), ITEMS[ITEM] = "WATER BOTTLE 1L", ITEMS[RECEIPT_NO] = _ReceiptNo))VAR _FilterTable = ADDCOLUMNS(SUMMARIZE(FILTER( _SummData, [HasBottle] = "WATER BOTTLE 1L" && [ITEM] <> "WATER BOTTLE 1L"),[ITEM]),"CountItem",VAR _Item = [ITEM]RETURN COUNTX( FILTER( _SummData, [HasBottle] = "WATER BOTTLE 1L" && [ITEM] = _Item), [ITEM] ))RETURN TOPN(2, _FilterTable, [CountItem], DESC)Replaced Distinct with MAXI would advise doing this in Power Query.
