Slow-performing report in Paginated Reports

krzysztof · ‎03-23-2025

Below, I will present the details of my report. First, I will ask the most important questions:

Why is the report running slowly?
Should I further limit the dataset in the query? For example, the dataset stores data for the last 4 years, but users generate reports for a maximum of the last year.
Did I use the right method to build the report?

Report details:

The data source is a semantic model in the Premium Fabric workspace. The semantic model is connected via DirectQuery to an analytical cube in Azure Analysis Services.

It was easier for me to connect to the semantic model there because, in Power BI Desktop, I created the necessary measures used in this report. These are simple measures using the SUM or DIVIDE syntax.

The report source looks as follows:

DAX Source:

DEFINE
    VAR __DS0FilterTable =
        FILTER (
            KEEPFILTERS ( VALUES ( 'DWH DimLocalization'[ChannelPL] ) ),
            NOT ( 'DWH DimLocalization'[ChannelPL] IN { "XX", "YY" } )
        )
    VAR __DS0FilterTable2 =
        TREATAS ( { "DD", "YY" }, 'DWH DimLocalization'[Board] )
    VAR __DS0FilterTable4 =
        FILTER (
            KEEPFILTERS ( VALUES ( 'DWH DimLocalization'[NewStrefaName] ) ),
            NOT ( 'DWH DimLocalization'[NewStrefaName]
                IN { "Austria", "Kosowo", "Niemcy", "Outlet RO", "Rosja", "Ukraina" } )
        )
    VAR __DS0FilterTable5 =
        FILTER (
            KEEPFILTERS ( VALUES ( 'DimProductSK'[AssortmentName] ) ),
            NOT ( 'DimProductSK'[AssortmentName] IN { BLANK (), "DF" } )
        )
    VAR __DS0FilterTable6 =
        TREATAS (
            { "FD", "FD", "SDF", "DER" },
            'DWH DimWarehouse'[WarehouseVirtualName]
        )
    VAR __DS0FilterTable7 =
        DATESBETWEEN ( 'DWH DimDate'[DateName], @FromDWHDimDateDate, @TODWHDimDateDate )
    VAR __DS0FilterTable8 =
        FILTER (
            VALUES ( DimProductSK[ProductType] ),
            @ProductType = " All"
                || PATHCONTAINS ( @ProductType, 'DimProductSK'[ProductType] )
        )
    VAR __DS0FilterTable9 =
        FILTER (
            VALUES ( DimProductSK[Brand] ),
            @Brand = " All"
                || PATHCONTAINS ( @Brand, 'DimProductSK'[Brand] )
        )
    VAR __DS0FilterTable10 =
        FILTER (
            VALUES ( 'DWH DimLocalization'[ChannelPL] ),
            @ChannelPL = " All"
                || PATHCONTAINS ( @ChannelPL, 'DWH DimLocalization'[ChannelPL] )
        )
    VAR __DS0FilterTable11 =
        FILTER (
            VALUES ( 'DWH DimLocalization'[CountryPL] ),
            @CountryPL = " All"
                || PATHCONTAINS ( @CountryPL, 'DWH DimLocalization'[CountryPL] )
        )
    VAR __DS0FilterTable12 =
        FILTER (
            VALUES ( 'DWH DimLocalization'[DistrictName] ),
            @DistrictName = " All"
                || PATHCONTAINS ( @DistrictName, 'DWH DimLocalization'[DistrictName] )
        )
    VAR __DS0FilterTable13 =
        FILTER (
            VALUES ( 'DWH DimLocalization'[NewStrefaName] ),
            @NewStrefaName = " All"
                || PATHCONTAINS ( @NewStrefaName, 'DWH DimLocalization'[NewStrefaName] )
        )
    VAR __DS0FilterTable14 =
        FILTER (
            VALUES ( 'DimProductSK'[AssortmentName] ),
            @AssortmentName = " All"
                || PATHCONTAINS ( @AssortmentName, 'DimProductSK'[AssortmentName] )
        )
    VAR __DS0FilterTable15 =
        FILTER (
            VALUES ( 'DimProductSK'[Gender] ),
            @Gender = " All"
                || PATHCONTAINS ( @Gender, 'DimProductSK'[Gender] )
        )
    VAR __DS0FilterTable16 =
        FILTER (
            VALUES ( 'DimProductSK'[AssortmentGroupName] ),
            @AssortmentGroupName = " All"
                || PATHCONTAINS ( @AssortmentGroupName, 'DimProductSK'[AssortmentGroupName] )
        )
    VAR __DS0FilterTable17 =
        FILTER (
            VALUES ( 'DimProductSK'[UpperMaterial] ),
            @UpperMaterial = " All"
                || PATHCONTAINS ( @UpperMaterial, 'DimProductSK'[UpperMaterial] )
        )
    VAR __DS0FilterTable18 =
        FILTER (
            VALUES ( 'DimProductSK'[DepartmentName] ),
            @DepartmentName = " All"
                || PATHCONTAINS ( @DepartmentName, 'DimProductSK'[DepartmentName] )
        )
    VAR __DS0FilterTable19 =
        FILTER (
            VALUES ( 'DimProductSK'[ProductCategoryName] ),
            @ProductCategoryName = " All"
                || PATHCONTAINS ( @ProductCategoryName, 'DimProductSK'[ProductCategoryName] )
        )
    VAR __DS0FilterTable20 =
        FILTER (
            VALUES ( 'DimProductSK'[Collection_Slicer] ),
            @Collection = " All"
                || PATHCONTAINS ( @Collection, 'DimProductSK'[Collection_Slicer] )
        )
    VAR __DS0FilterTable21 =
        FILTER (
            VALUES ( 'DimProductSK'[Season] ),
            @season = " All"
                || PATHCONTAINS ( @season, 'DimProductSK'[Season] )
        )
    VAR __DS0FilterTable23 =
        FILTER (
            VALUES ( 'DimProductSK'[Exposure] ),
            @Exposure = " All"
                || PATHCONTAINS ( @Exposure, 'DimProductSK'[Exposure] )
        )
    VAR __DS0FilterTable24 =
        FILTER (
            VALUES ( 'DWH V_DimProperty'[Value] ),
            @VALUE = " All"
                || PATHCONTAINS ( @VALUE, 'DWH V_DimProperty'[Value] )
        )
    VAR __DS0FilterTable25 =
        FILTER (
            VALUES ( 'DimProductSK'[SourceCountry] ),
            @SourceCountry = " All"
                || PATHCONTAINS ( @SourceCountry, 'DimProductSK'[SourceCountry] )
        )
    VAR __DS0FilterTable26 =
        FILTER (
            VALUES ( 'DimProductSK'[OwnBrand] ),
            @OwnBrand = " All"
                || PATHCONTAINS ( @OwnBrand, 'DimProductSK'[OwnBrand] )
        )
    VAR __DS0FilterTable27 =
        FILTER (
            VALUES ( 'DimProductSK'[Vendor] ),
            @VENDOR = " All"
                || PATHCONTAINS ( @VENDOR, 'DimProductSK'[Vendor] )
        )
    VAR __DS0FilterTable28 =
        FILTER (
            VALUES ( 'DimProductSK'[ProductSubType] ),
            @ProductSubType = " All"
                || PATHCONTAINS ( @ProductSubType, 'DimProductSK'[ProductSubType] )
        )
    VAR __DS0FilterTable29 =
        FILTER (
            VALUES ( 'DimProductSK'[EANCode] ),
            @EANCode = "*"
                || PATHCONTAINS ( @EANCode, 'DimProductSK'[EANCode] )
        )
    VAR __DS0FilterTable30 =
        FILTER (
            VALUES ( 'DimProductSK'[ModelColorName] ),
            @ModelColorName = "*"
                || PATHCONTAINS ( @ModelColorName, 'DimProductSK'[ModelColorName] )
        )
    VAR __DS0FilterTable31 =
        FILTER (
            VALUES ( 'DWH DimLocalization'[StoreName] ),
            @StoreName = " All"
                || PATHCONTAINS ( @StoreName, 'DWH DimLocalization'[StoreName] )
        )
    VAR __DS0FilterTable32 =
        FILTER (
            VALUES ( 'DWH DimLocalization'[Board] ),
            @Board = " All"
                || PATHCONTAINS ( @Board, 'DWH DimLocalization'[Board] )
        )
    VAR __DS0Core =
        SUMMARIZECOLUMNS (
            'DimProductSK'[ModelColorName],
            'DimProductSK'[ModelcolorIDReference],
            'DimProductSK'[Link_Details_ProductMain],
            'DimProductSK'[EANCode],
            'DimProductSK'[Collection_Slicer],
            'DimProductSK'[Collection Timeliness],
            'DimProductSK'[Season],
            'DimProductSK'[Intake],
            'DimProductSK'[Brand],
            'DimProductSK'[DepartmentName],
            'DimProductSK'[ProductType],
            'DimProductSK'[ProductSubType],
            'DimProductSK'[isOnLine],
            'DWH DimLocalization'[Board],
            __DS0FilterTable,
            __DS0FilterTable2,
            __DS0FilterTable4,
            __DS0FilterTable5,
            __DS0FilterTable6,
            __DS0FilterTable7,
            __DS0FilterTable8,
            __DS0FilterTable9,
            __DS0FilterTable10,
            __DS0FilterTable11,
            __DS0FilterTable12,
            __DS0FilterTable13,
            __DS0FilterTable14,
            __DS0FilterTable15,
            __DS0FilterTable16,
            __DS0FilterTable17,
            __DS0FilterTable18,
            __DS0FilterTable19,
            __DS0FilterTable20,
            __DS0FilterTable21,
            __DS0FilterTable23,
            __DS0FilterTable24,
            __DS0FilterTable25,
            __DS0FilterTable26,
            __DS0FilterTable27,
            __DS0FilterTable28,
            __DS0FilterTable29,
            __DS0FilterTable30,
            __DS0FilterTable31,
            __DS0FilterTable32,
            "Revenue_Net_PLN_SUM", 'DM_SALES FactSalesDaily'[Revenue_Net_PLN_SUM],
            "Zdjecie", CALCULATE ( MAX ( DimProductSK[MainImage] ) ) & "&rsct=image/jpeg",
            "Units_SUM", 'DM_SALES FactSalesDaily'[Units_SUM],
            "Units_Detal", 'DM_SALES FactSalesDaily'[Units_Detal],
            "Units_Ecommerce", 'DM_SALES FactSalesDaily'[Units_Ecommerce],
            "UnitsSD",
                CALCULATE (
                    'DM_SALES FactSalesDaily'[Units_SUM],
                    FILTER (
                        ALL ( 'DWH DimLocalization' ),
                        'DWH DimLocalization'[Board] = "DD"
                            || 'DWH DimLocalization'[Board] = "FF"
                    ),
                    ALL ( 'DWH DimLocalization'[ChannelPL] ),
                    ALL ( 'DWH DimLocalization'[CountryPL] ),
                    ALL ( 'DWH DimLocalization'[NewStrefaName] ),
                    ALL ( 'DimProductSK'[SystemType] ),
                    ALL ( 'DimProductSK'[AvalSD] ),
                    ALL ( 'DWH DimWarehouse'[WarehouseVirtualName] )
                ),
            "DM_Store_OnLocalization_EOP", 'DWH V_FactInventoryStore_SK_Calculated'[DM_Store_OnLocalization_EOP],
            "Warehouse_OnHand_Ecom_EOP", 'DWH FactInventoryWarehouse'[Warehouse_OnHand_Ecom_EOP],
            "DM_Product_EcommerceDostepne", 'DM_PRODUCT FactInventory'[DM_Product_EcommerceDostepne],
            "Deposit_OnHand_EOP", 'DWH FactInventoryDeposit'[Deposit_OnHand_EOP],
            "Warehouse_OnHand_EOP", '_Measure'[Na_magazynach_total],
            "DM_Product_WarehouseTransferIN", 'DM_PRODUCT FactInventory'[DM_Product_WarehouseTransferIN],
            "DM_Product_OnHandAlocation", 'DM_PRODUCT FactInventory'[DM_Product_OnHandAlocation],
            "DM_Product_ReservationAlocation", 'DM_PRODUCT FactInventory'[DM_Product_ReservationAlocation],
            "Warehouse_Orders_Ecom_EOP", 'DWH FactInventoryWarehouse'[Warehouse_Orders_Ecom_EOP],
            "Total_Old_Stock", '_Measure'[Total_Old_Stock],
            "DM_Product_Warehouse_BlockOther", 'DM_PRODUCT FactInventory'[DM_Product_Warehouse_BlockOther],
            "Magazyny_pozostałe", '_Measure'[Magazyny pozostałe],
            "DM_Total_InTransit_EOP", 'DWH V_FactInventoryStore_SK_Calculated'[DM_Total_InTransit_EOP],
            "DM_Store_InTransit_EOP", 'DWH V_FactInventoryStore_SK_Calculated'[DM_Store_InTransit_EOP],
            "Warehouse_InTransit_EOP", 'DWH FactInventoryWarehouse'[Warehouse_InTransit_EOP],
            "Warehouse_InTransit_Ecom_EOP", 'DWH FactInventoryWarehouse'[Warehouse_InTransit_Ecom_EOP],
            "Deposit_InTransit_EOP", 'DWH FactInventoryDeposit'[Deposit_InTransit_EOP],
            "DM_Total_EOP", '_Measure'[Stan_ilosciowy_total],
            "Skutecznosc_total", '_Measure'[Skutecznosc_total],
            "Skutecznosc_sklepy_detal", '_Measure'[Skutecznosc_sklepy_detal],
            "Skutecznosc_Ecom", '_Measure'[Skutecznosc_Ecom],
            "Stock_Cover_Total", '_Measure'[Stock_Cover_Total],
            "Stock_Cover_Sklepy", '_Measure'[Stock_Cover_Sklepy],
            "Stock_Cover_Ecom", '_Measure'[Stock_Cover_Ecom],
            "Margin_Pct_InitialPrice_PL_LC", 'DM_PRODUCT FactPrices'[Margin_Pct_InitialPrice_PL_LC],
            "Margin_Pct_PLN", 'DM_SALES FactSalesDaily'[Margin_Pct_PLN],
            "InitialPrice_PL_LC", 'DM_PRODUCT FactPrices'[InitialPrice_PL_LC],
            "CurrentPrice_PL_LC", 'DM_PRODUCT FactPrices'[CurrentPrice_PL_LC],
            "Discount_PL_LC", 'DM_PRODUCT FactPrices'[Discount_PL_LC],
            "Discount___PL_LC", 'DM_PRODUCT FactPrices'[Discount_%_PL_LC],
            "CostPrice_AVG_EOP", 'DWH V_FactCOGS'[CostPrice_AVG_EOP],
            "Cost_Price_Gross_AVG_ALL", 'DWH V_FactCostPrices'[Cost_Price_Gross_AVG_ALL],
            "OrderValue_PLN_SUM_toDate", 'DWH V_FactPurchaseOrders'[OrderValue_PLN_SUM_toDate],
            "OrderUnits_SUM_toDate", 'DWH V_FactPurchaseOrders'[OrderUnits_SUM_toDate],
            "ToDelivery_Value_PLN_SUM_toDate", 'DWH V_FactPurchaseOrders'[ToDelivery_Value_PLN_SUM_toDate],
            "ToDelivery_Units_toDate", 'DWH V_FactPurchaseOrders'[ToDelivery_Units_toDate],
            "ETA_MAX", 'DWH V_FactShipments'[ETA_MAX],
            "DeliveryDateID_MAX", [DeliveryDateID_MAX],
            "ETA_Warehouse_MAX", 'DWH V_FactShipments'[ETA_Warehouse_MAX],
            "FirstReceiveMKDate_measue", '_Measure'[FirstReceiveMKDate_measue],
            "PriceDate_MAX", 'DM_PRODUCT FactPrices'[PriceDate_MAX],
            "FirstAllocatedDate", 'DWH V_FactShipmentsDate'[FirstAllocatedDate],
            "Depozyt_Hurt_zablokowane", '_Measure'[Depozyt_Hurt_zablokowane],
            "Wdrodze_Hurt_zablokowane", '_Measure'[Wdrodze_Hurt_zablokowane],
            "OnHand_Old_Stock",
                CALCULATE (
                    [Warehouse_OnHand_EOP] - ( [Warehouse_BlockForSuppliers_EOP] + [Warehouse_BlockForTransfer_EOP] + [Warehouse_BlockOther_EOP] ),
                    'DWH DimWarehouse'[WarehouseVirtualName] = "DF"
                ),
            "Check",
                'DWH V_FactInventoryStore_SK_Calculated'[DM_Total_EOP] + 'DWH V_FactPurchaseOrders'[OrderUnits_SUM_toDate],
            "Check_1",
                'DM_SALES FactSalesDaily'[Revenue_Net_PLN_SUM] + 'DWH V_FactInventoryStore_SK_Calculated'[DM_Total_EOP]
        )

EVALUATE
__DS0Core

The report has defined fields.

And it has defined parameters:

For each slicer, the report has separate datasets with applied filters to avoid displaying unnecessary data:

The visualization has filtering applied to hide rows that do not meet the condition"

v-vpabbu · ‎04-02-2025

Hi @krzysztof,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Vinay Pabbu

v-vpabbu · ‎04-07-2025

Hi @krzysztof,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,
Vinay Pabbu

v-vpabbu

Hi @krzysztof,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Vinay Pabbu

v-vpabbu · ‎03-25-2025

Hi @krzysztof,

Thank you for reaching out to Microsoft Fabric Community Forum.

Below are some suggestions to improve the performance of your Power BI reports and optimize data processing.

Since reports are generated for a maximum of one year, restrict data retrieval to the last year in the query and apply row-level filtering at the source to minimize data processing.
Remove unnecessary filters and columns by ensuring only essential filters are applied and eliminating unused columns that are not required for display or calculations.
If possible, create aggregated tables in Azure Analysis Services to reduce the workload for DirectQuery.
Use Power BI’s Performance Analyzer to identify slow queries and determine if the bottleneck is caused by DirectQuery from Azure Analysis Services.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

Regards,
Vinay Pabbu