Forum Discussion
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...
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
Super User
Super UserChange your table visual to its simplest form. Only the columns you need, no row totals. Then the DAX query will be much simpler.