Slow paginated report - The connection either timed out or was lost. Pareto Analysis

asparagus1_ — Tue, 11 Mar 2025 20:54:00 GMT

I have a dataset in a Paginated Report that runs slowly because it includes a Pareto analysis of products on a table with around 200,000 rows. When I analyze its DAX query, it takes approximately 10 minutes to execute. Of course, this is not an ideal execution time, but it is what it is.

The problem is that in the Paginated Report, even when I run the report with filters on the product table, it is unable to generate at all. I receive the following error:
"The connection either timed out or was lost. Query execution failed for dataset 'XXX'."

Is there anything I can do about this, or do I just have to keep optimizing the dataset's code?

This is the code:

DEFINE
VAR _TotalSales =
CALCULATE ( [Sales $], ALLSELECTED ( DimProducts[ID_Product] ) )

VAR _SortedProducts =
ADDCOLUMNS (
ALLSELECTED ( DimProducts[ID_Product] ),
"Sales", [Sales $]
)

VAR _SortedProductsRanked =
ADDCOLUMNS (
_SortedProducts,
"Rank", RANKX ( _SortedProducts, [Sales],, DESC, DENSE )
)

VAR _CumulativeSalesTable =
ADDCOLUMNS (
_SortedProductsRanked,
"CumulativeSales",
VAR CurrentRank = [Rank]
RETURN
SUMX (
FILTER ( _SortedProductsRanked, [Rank] <= CurrentRank ),
[Sales]
)
)

VAR _ProductsWithin80Percent =
FILTER (
_CumulativeSalesTable,
DIVIDE ( [CumulativeSales], _TotalSales, 0 ) <= 0.8
)

EVALUATE _ProductsWithin80Percent

Re: Slow paginated report - The connection either timed out or was lost. Pareto Analysis

Anonymous — Wed, 12 Mar 2025 06:30:34 GMT

Hi asparagus1_ ,

You can refer the following link to optimize the query first:

DAX Query Optimization Techniques for Faster Calculations in Power BI: Advanced Guide

DEFINE VAR _TotalSales = CALCULATE ( [Sales $], REMOVEFILTERS ( DimProducts[ID_Product] ) ) VAR _ProductSales = SUMMARIZE ( ALLSELECTED ( DimProducts ), DimProducts[ID_Product], "Sales", [Sales $] ) VAR _SortedProductsRanked = ADDCOLUMNS ( _ProductSales, "Rank", RANKX ( _ProductSales, [Sales],, DESC, DENSE ) ) VAR _CumulativeSalesTable = ADDCOLUMNS ( _SortedProductsRanked, "CumulativeSales", VAR CurrentSales = [Sales] VAR CurrentRank = [Rank] RETURN SUMX ( FILTER ( _SortedProductsRanked, [Rank] <= CurrentRank ), [Sales] ) ) EVALUATE FILTER ( _CumulativeSalesTable, DIVIDE ( [CumulativeSales], _TotalSales, 0 ) <= 0.8 )

And increase the Query Timeout:

Set time-out values for Power BI paginated report dataset processing - Power BI | Microsoft Learn

Best Regards

topic Slow paginated report - The connection either timed out or was lost. Pareto Analysis in Developer

Slow paginated report - The connection either timed out or was lost. Pareto Analysis

Re: Slow paginated report - The connection either timed out or was lost. Pareto Analysis