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_...
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 bottle1L
VAR _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 count
VAR _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 Table
ITEMRECEIPT_NO
| WATER 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 talespin , thanks for help... and i trying to run your DAX but i got this error "Resource Governing: This query uses more memory than the configured limit"... have any faster way to run DAX?
My data have more than half of billion.
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.