Forum Discussion

danny3313's avatar
danny3313
Regular Visitor
2 years ago

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_...
talespin's avatar
talespin
Solution Sage
2 years ago

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 MAX

I would advise doing this in Power Query.

danny3313's avatar
danny3313
Regular Visitor
2 years ago

Hi talespin , result still same ðŸ˜ž

  • talespin's avatar
    talespin
    Solution Sage
    2 years ago

    hi danny3313 

     

    How many records you have in source table?

    • danny3313's avatar
      danny3313
      Regular Visitor
      2 years ago

      talespin more than half of billion 

      • talespin's avatar
        talespin
        Solution Sage
        2 years ago

        Hi danny3313 ,

         

        If you can aggregate data before bringing into Data model and reduce record count, that is one way. But if you really need 500Million records in Power BI Datamodel then try to do it in Power Query. If that too fails then your existing Database is the only option.

         

        Sorry I couldn't help.