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

[asparagus1_]

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 

[Anonymous · Accepted solution]

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