DAX Query taking high time to resolve

[Anonymous]

Hi, Can anyone help me to resolve this issue. This Dax query is taking very high time to run. My visual showing error sometime. Please help me to resolve it.

DEFINE
VAR __DS0FilterTable =
TREATAS({"All SKU"}, 'Top N SKU'[TopN])

VAR __ValueFilterDM1 =
FILTER(
KEEPFILTERS(
SUMMARIZECOLUMNS(
'Products'[SKU],
'Products'[Company],
'Products'[Master_Brand],
__DS0FilterTable,
"Weighted_dist", 'Table'[Weighted dist],
"Wighted_ROS", 'Table'[Wighted ROS],
"Value_Share", 'Table'[Value Share],
"Numeric_Dist_Products", 'Table'[Numeric Dist Products],
"Share_of_range_where_listed", 'Table'[Share of range where listed],
"Top_N_Dynamic_Viz_4_new_", IGNORE('Top N SKU'[Top N Dynamic Viz 4(new)])
)
),
SEARCH("include", [Top_N_Dynamic_Viz_4_new_], 1, 0) >= 1
)

VAR __DS0Core =
SUMMARIZECOLUMNS(
ROLLUPADDISSUBTOTAL(
ROLLUPGROUP('Products'[SKU], 'Products'[Company], 'Products'[Master_Brand]), "IsGrandTotalRowTotal"
),
__DS0FilterTable,
__ValueFilterDM1,
"Weighted_dist", 'Table'[Weighted dist],
"Wighted_ROS", 'Table'[Wighted ROS],
"Value_Share", 'Table'[Value Share],
"Numeric_Dist_Products", 'Table'[Numeric Dist Products],
"Share_of_range_where_listed", 'Table'[Share of range where listed]
)

VAR __DS0PrimaryWindowed =
TOPN(
502,
__DS0Core,
[IsGrandTotalRowTotal],
0,
[Weighted_dist],
0,
'Products'[SKU],
1,
'Products'[Company],
1,
'Products'[Master_Brand],
1
)

EVALUATE
__DS0PrimaryWindowed

ORDER BY
[IsGrandTotalRowTotal] DESC,
[Weighted_dist] DESC,
'Products'[SKU],
'Products'[Company],
'Products'[Master_Brand]

[Anonymous]

Hi  @Anonymous ,

This is the related document, you can view this content:

Here is a link on power bi Dax optimization, you can view this content:

https://maqsoftware.com/insights/dax-best-practices

https://community.powerbi.com/t5/Desktop/DAX-and-Power-BI-Optimization/m-p/268589

https://blog.enterprisedna.co/measure-in-power-bi-optimization-tips-and-techniques/#:~:text=When%20you%E2%80%99re%20trying%20to%20optimize%20a%20measure%20in,that%20the%20total%20execution%20time%20is%203.6%20seconds.

https://community.powerbi.com/t5/Community-Blog/Performance-Tuning-DAX-Part-1/ba-p/976275

You can check the query duration by Performance Analyzer option in Power BI Desktop.

For reference: Use Performance Analyzer to examine report element performance

Or, you can try to update your code by DAX Studio by checking the resource occupancy.

https://daxstudio.org/

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.