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
    7 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!!

v-sathmakuri's avatar
v-sathmakuri
Icon for Community Support rankCommunity Support
7 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!!

j_ocean's avatar
j_ocean
Icon for Helper V rankHelper V
7 days ago

v-sathmakuri​ Thank you for your response, two follow up questions:

  1. Can you please go into some more detail about how a query pulling a subset of data from a single table on a 10MB semantic model runs up to 15+MB of data? Esp given that the point at which it fails is roughly 20% of the expected rows and maybe a quarter of the available columns. Is the connector just super chatty, making lots of recursive calls for some reason? The table is about as straightforward as one could ask for, no measures, no totals, no crossing a relationship.
  2. What is the best method to append the data in power automate before the create file step?
  • v-sathmakuri's avatar
    v-sathmakuri
    Icon for Community Support rankCommunity Support
    6 days ago

    Hi j_ocean​ ,

    The 15 MB limit concerns the serialized query response, rather than the 10 MB semantic-model size. Recursive or chatter calls are not necessarily the cause. Because text and other values can significantly increase the size of the returned JSON compared with the compressed model, rows are truncated once the response reaches 15 MB.

    In Power Automate:

    • Initialize an Array variable, AllRows = [].
    • Run the DAX in ID/date-based batches, such as 20K rows per batch.
    • For each query result, use Apply to each -> Append to array variable (AllRows).
    • After processing all batches, use Create CSV table with AllRows.
    • Finally, use Create file to create a single CSV.

     

    Thanks!!