Forum Discussion

j_ocean's avatar
j_ocean
Icon for Helper V rankHelper V
8 days ago
Solved

DAX Not Returning All Rows

I have for a long time been using DAX expressions copied from PBI Desktop Performance Analyzer into Power Automate's "Run a Query Against a Dataset" action to extract data from Power BI Semantic Mode...
  • v-sathmakuri's avatar
    v-sathmakuri
    8 days ago

    Hi j_ocean​ ,

    Thank you for reaching out to fabric community.

    The issue is most likely the 15 MB query-response limit in the Power BI query API not TOPN or your DAX.

    • Keep the DAX without TOPN. 
    • Split the query into smaller batches using an ID/date filter. 
    • Append the results from all queries into one Power Automate array. 
    • Use Create CSV Table once to generate a single CSV. 

    The 100K - row / 1M-value limits are not the only limits; the 15 MB response limit can truncate the result earlier.

    Thanks!!

maruthisp's avatar
maruthisp
Icon for Super User rankSuper User
7 days ago

Hi j_ocean​,

Please try the following steps:

  1. Check the raw output: Open the Power Automate run history and check the output of the Run a query against a dataset action.
  2. Check the API limits: Power BI Execute Queries has a limit of 100,000 rows, 1,000,000 values, or 15 MB per query, whichever is reached first.
  3. Reduce the response size: Use SELECTCOLUMNS() in your DAX query to return only the required columns.
  4. Check the CSV action: If all expected rows are present in the query output, check the Create CSV table action for missing rows.
  5. Use batching if required: If the query still exceeds the limits, split the data into smaller batches using a unique ID and combine the results into one CSV file.

    Best Regards,
    Maruthi
    LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/
    X            -  Maruthi Siva Prasad - (@MaruthiSP) / X