I have created a table chart using a field parameter with more than 1000 rows in Power BI. In Power Automate, I am using the "Run a Query against a Dataset" action, and I require assistance in modifying the DAX query.
Current DAX Query:
-----------------------------------------------------------------------------------
// 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 am seeking guidance on how to optimize this query to handle large datasets efficiently and improve performance.
Any suggestions or insights would be greatly appreciated.
Thank you for your assistance!
