Forum Discussion

j_ocean's avatar
j_ocean
Icon for Helper V rankHelper V
2 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 Models to CSVs. Lately, it no longer seems to be working, cutting off at an arbitrary number in the 10-20k row range.

Things I've tried:

  1. Changed DAX "evaluate" to "__DS0Core", to avoid the TopN call that PBI adds. This does avoid the 500 row limit but doesn't return the full list. I emphasize, it used to. This is SOP for us.
  2. Changed DAX to evaluate the windowed variable but changed the TopN to 100k. No effect.
  3. Changed the visual in PBI from which the DAX was developed to
    1. Show rows with no data. No such rows exist but tried anyway, no effect. This also created new variables in the code, I tried them all in the evaluate step. No effect.
    2. Only include columns from one table. No effect.
    3. Ensured no measures are being called, all variables are native PBI columns.
    4. Added enough detail to ensure every row I'm expecting would show up as unique (added an ID column). No effect.
  4. Checked against Power Automate Limits
    1. I'm below the 100k row limit.
    2. I'm extracting few enough columns that I'm not breaking the 1M cell limit.
    3. The entire data model is below the 15 MB limit, let alone this sample of data.
  5. Disabled "allow chunking," whatever that means. No effect.
  6. Checked the CSVs for line breaks getting messed up by special characters in text fields. Nothing apparent.

The only thing that did work was breaking the data call up into several individual DAX queries each below ~20k rows but this is clunky and a poor solution that yields multiple csvs when one should suffice.

  • v-sathmakuri's avatar
    v-sathmakuri
    2 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!!

4 Replies

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

  • maruthisp's avatar
    maruthisp
    Icon for Super User rankSuper User
    1 day 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