Forum Discussion

j0710's avatar
j0710
Frequent Visitor
2 years ago

How to Export More Than 1000 Rows from a Power BI Report to a CSV File in Power Automate?

Question: I'm currently working on a Power Automate flow where I need to export data from a Power BI report to a CSV file. However, I'm encountering a limitation where only 1000 rows of data are be...
admin
capacities
power bi
Real-time Analytics
lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
2 years ago

Consider using "Run a Query against a Dataset" instead, and use your Power Automate visual only to convey the filters.

  • j0710's avatar
    j0710
    Frequent Visitor
    2 years ago
    I have created a table chart using a field parameter with more than 1000 rows in Power BI. I am using the "Run a Query against a Dataset" action in Power Automate, and I need assistance in modifying the DAX query.
     
    Here is the DAX query I am currently using:
    -------------------------------------------------------------------------------
    // DAX Query
    DEFINE
    VAR __DS0Core = 
    SUMMARIZECOLUMNS(
    ROLLUPADDISSUBTOTAL(
    ROLLUPGROUP(
    Table_Name[PolicyTypeCode],
    Table_Name[Channel],
    Table_Name[Executive Code],
    Table_Name[Executive Name],
    Table_Name[BrokerExecCode],
    Table_Name[BrokerExectiveName],
    'GIM_Branch'[Branch Name],
    Table_Name[Motor & Non Motor],
    Table_Name[PolicyTypeDesc],
    'Vehicle_Info'[PrdClassCode],
    'Vehicle_Info'[PrdClassDesc]
    ), "IsGrandTotalRowTotal"
    ),
    "count_NOP", Table_Name[count NOP],
    "Countpkpoliid", IGNORE(
    CALCULATE(COUNTA(Table_Name[pkpoliid]))
    )
    )
     
    VAR __DS0PrimaryWindowed = 
    TOPN(
    502,
    __DS0Core,
    [IsGrandTotalRowTotal],
    0,
    Table_Name[PolicyTypeCode],
    1,
    Table_Name[Channel],
    1,
    Table_Name[Executive Code],
    1,
    Table_Name[Executive Name],
    1,
    Table_Name[BrokerExecCode],
    1,
    Table_Name[BrokerExectiveName],
    1,
    'GIM_Branch'[Branch Name],
    1,
    Table_Name[Motor & Non Motor],
    1,
    Table_Name[PolicyTypeDesc],
    1,
    'Vehicle_Info'[PrdClassCode],
    1,
    'Vehicle_Info'[PrdClassDesc],
    1
    )
     
    VAR __DS0CoreNoInstanceFiltersNoTotals = 
    FILTER(KEEPFILTERS(__DS0Core), [IsGrandTotalRowTotal] = FALSE)
     
    EVALUATE
    GROUPBY(
    __DS0CoreNoInstanceFiltersNoTotals,
    "Mincount_NOP", MINX(CURRENTGROUP(), [count_NOP]),
    "Maxcount_NOP", MAXX(CURRENTGROUP(), [count_NOP]),
    "MinCountpkpoliid", MINX(CURRENTGROUP(), [Countpkpoliid]),
    "MaxCountpkpoliid", MAXX(CURRENTGROUP(), [Countpkpoliid])
    )
     
    EVALUATE
    __DS0PrimaryWindowed
     
    ORDER BY
    [IsGrandTotalRowTotal] DESC,
    Table_Name[PolicyTypeCode],
    Table_Name[Channel],
    Table_Name[Executive Code],
    Table_Name[Executive Name],
    Table_Name[BrokerExecCode],
    Table_Name[BrokerExectiveName],
    'GIM_Branch'[Branch Name],
    Table_Name[Motor & Non Motor],
    Table_Name[PolicyTypeDesc],
    'Vehicle_Info'[PrdClassCode],
    'Vehicle_Info'[PrdClassDesc]
     
     
    // DAX Query 
    DEFINE
    VAR __DS0Core = 
    SUMMARIZE(
    'FIELD_PARAMETER',
    'FIELD_PARAMETER'[FIELD_PARAMETER Fields],
    'FIELD_PARAMETER'[FIELD_PARAMETER Order],
    'FIELD_PARAMETER'[FIELD_PARAMETER]
    )
     
    VAR __DS0BodyLimited = 
    TOPN(
    152,
    __DS0Core,
    'FIELD_PARAMETER'[FIELD_PARAMETER Order],
    1,
    'FIELD_PARAMETER'[FIELD_PARAMETER Fields],
    1,
    'FIELD_PARAMETER'[FIELD_PARAMETER],
    1
    )
     
    EVALUATE
    __DS0BodyLimited
     
    ORDER BY
    'FIELD_PARAMETER'[FIELD_PARAMETER Order],
    'FIELD_PARAMETER'[FIELD_PARAMETER Fields],
    'FIELD_PARAMETER'[FIELD_PARAMETER]
    -------------------------------------------------------------------------------
     
    I would appreciate any guidance or suggestions on how to modify this query to improve performance and handle large datasets efficiently.
     
    Thank you in advance for your help!
    • lbendlin's avatar
      lbendlin
      Icon for Super User rankSuper User
      2 years ago

      Change your table visual to its simplest form.  Only the columns you need, no row totals.  Then the DAX query will be much simpler.