Slow query performance - Paginated reports

Harris · May 19, 2024, 11:47pm

I created a dax query in reports builder. The source of the data is the cube in AAS. Unfortunately, users entering the report generate a lot of power to the source of the cube.
Please support me on how I could improve my query or what else I could do? Paginated report is needed because we have photos that export to excel

EVALUATE
CALCULATETABLE (
    SUMMARIZECOLUMNS (
        DimProductSK[ModelColorName],
        DimProductSK[EANCode],
        DimProductSK[Collection_Slicer],
        DimProductSK[Season],
        DimProductSK[Intake],
        DimProductSK[Brand],
        DimProductSK[DepartmentName],
        DimProductSK[ProductType],
        DimProductSK[isOnLine],
        KEEPFILTERS (
            FILTER (
                ALL ( 'DWH DimLocalization'[ChannelPL] ),
                NOT ( 'DWH DimLocalization'[ChannelPL]
                    IN { "Rozchody", " Hurt B2B", "Hurt obcy", "Marketplace" } )
            )
        ),
        KEEPFILTERS (
            FILTER (
                ALL ( 'DWH DimLocalization'[CountryPL] ),
                NOT ( 'DWH DimLocalization'[CountryPL]
                    IN { "Turcja", "Bangladesz", "Mongolia", "Białoruś", "Indie", "Meksyk" } )
            )
        ),
        KEEPFILTERS (
            FILTER (
                ALL ( 'DWH DimLocalization'[NewStrefaName] ),
                NOT ( 'DWH DimLocalization'[NewStrefaName]
                    IN {
                        "Austria",
                        "Kosowo",
                        "Niemcy",
                        "Outlet RO",
                        "Rosja",
                        "Turcja",
                        "Ukraina"
                    } )
            )
        ),
        KEEPFILTERS (
            FILTER (
                ALL ( 'DWH DimLocalization'[Board] ),
                'DWH DimLocalization'[Board] = "LLL"
            )
        ),
        KEEPFILTERS (
            FILTER ( ALL ( DimProductSK[SystemType] ), DimProductSK[SystemType] = "LLL" )
        ),
        KEEPFILTERS (
            FILTER (
                ALL ( 'DWH V_FactPurchaseOrders'[OrderStatus] ),
                'DWH V_FactPurchaseOrders'[OrderStatus] <> "0"
            )
        ),
        KEEPFILTERS (
            FILTER (
                ALL ( 'DWH DimLocalization'[Floors] ),
                NOT ( 'DWH DimLocalization'[Floors] IN { "Sklep wzorcowy" } )
            )
        ),
        KEEPFILTERS (
            FILTER (
                ALL ( DimProductSK[AssortmentID] ),
                DimProductSK[AssortmentID] <> 4000
            )
        ),
        KEEPFILTERS (
            FILTER (
                ALL ( DimProductSK[AssortmentID] ),
                DimProductSK[AssortmentID] <> 9000
            )
        ),
        KEEPFILTERS (
            FILTER ( ALL ( DimProductSK[AssortmentID] ), DimProductSK[AssortmentID] <> -2 )
        ),
        KEEPFILTERS (
            FILTER ( ALL ( DimProductSK[AssortmentID] ), DimProductSK[AssortmentID] <> -1 )
        ),
        --KEEPFILTERS( FILTER( ALL( DimProductSK[ProductType] ), NOT( 'DimProductSK[ProductType] IN {"0","Reklamówka foliowa","Reklamówka papierowa"} ))),
        KEEPFILTERS (
            FILTER (
                ALL ( 'DWH DimWarehouse'[WarehouseVirtualName] ),
                ( 'DWH DimWarehouse'[WarehouseVirtualName]
                    IN {
                        "Magazyn LLL EU - Sklad Celny",
                        "Magazyn LLL S.A - Dystrybucyjny",
                        "Magazyn LLL S.A. - Przyjęć",
                        "Magazyn Centralny - Magazyn przyjec",
                        "Magazyn Centralny - Oclone",
                        "Magazyn Centralny - Old Stock",
                        "Magazyn Centralny - Przepakownia",
                        "Magazyn Centralny - W trakcie prac wewnetrznych",
                        "Magazyn Centralny - Wzorcownia",
                        "Magazyn Centralny - Zwózka Ecommerce",
                        "Magazyn Ecommerce"
                    } )
            )
        ),
        "Units_SUM", [Units_SUM],
        "Units_Detal", [Units_Detal],
        "Revenue_Net_PLN_SUM", [Revenue_Net_PLN_SUM],
        "Units_Ecommerce", [Units_Ecommerce],
        "Store_OnLocalization_EOP", [Store_OnLocalization_EOP],
        "DM_Product_Ecommerce", [DM_Product_Ecommerce],
        "Deposit_OnHand_EOP", [Deposit_OnHand_EOP],
        "DM_Product_WarehouseTransferIN", [DM_Product_WarehouseTransferIN],
        "DM_Product_OnHandAlocation", [DM_Product_OnHandAlocation],
        "Warehouse_OnHand_EOP", [Warehouse_OnHand_EOP],
        "Total_InTransit_EOP", [Total_InTransit_EOP],
        "Store_InTransit_EOP", [Store_InTransit_EOP],
        "Warehouse_InTransit_EOP", [Warehouse_InTransit_EOP],
        "Deposit_InTransit_EOP", [Deposit_InTransit_EOP],
        "Stock_Cover_Total",
            VAR ID_Start =
                MIN ( 'DWH DimDate'[DateID] )
            VAR ID_End =
                MAX ( 'DWH DimDate'[DateID] )
            VAR DateStart =
                DATE ( INT ( LEFT ( ID_Start, 4 ) ), INT ( MID ( ID_Start, 5, 2 ) ), INT ( RIGHT ( ID_Start, 2 ) ) )
            VAR DateEnd =
                DATE ( INT ( LEFT ( ID_End, 4 ) ), INT ( MID ( ID_End, 5, 2 ) ), INT ( RIGHT ( ID_End, 2 ) ) )
            VAR countDays =
                DATEDIFF ( DateStart, DateEnd, DAY ) + 1
            RETURN
                CALCULATE ( DIVIDE ( [DM_Total_EOP], [Units_SUM], 0 ) * countDays ),
        "Stock_Cover_Sklepy",
            VAR ID_Start =
                MIN ( 'DWH DimDate'[DateID] )
            VAR ID_End =
                MAX ( 'DWH DimDate'[DateID] )
            VAR DateStart =
                DATE ( INT ( LEFT ( ID_Start, 4 ) ), INT ( MID ( ID_Start, 5, 2 ) ), INT ( RIGHT ( ID_Start, 2 ) ) )
            VAR DateEnd =
                DATE ( INT ( LEFT ( ID_End, 4 ) ), INT ( MID ( ID_End, 5, 2 ) ), INT ( RIGHT ( ID_End, 2 ) ) )
            VAR countDays =
                DATEDIFF ( DateStart, DateEnd, DAY ) + 1
            RETURN
                CALCULATE (
                    DIVIDE ( [Store_OnLocalization_EOP], [Units_Detal], 0 ) * countDays
                ),
        "Stock_Cover_Ecom",
            VAR ID_Start =
                MIN ( 'DWH DimDate'[DateID] )
            VAR ID_End =
                MAX ( 'DWH DimDate'[DateID] )
            VAR DateStart =
                DATE ( INT ( LEFT ( ID_Start, 4 ) ), INT ( MID ( ID_Start, 5, 2 ) ), INT ( RIGHT ( ID_Start, 2 ) ) )
            VAR DateEnd =
                DATE ( INT ( LEFT ( ID_End, 4 ) ), INT ( MID ( ID_End, 5, 2 ) ), INT ( RIGHT ( ID_End, 2 ) ) )
            VAR countDays =
                DATEDIFF ( DateStart, DateEnd, DAY ) + 1
            RETURN
                CALCULATE (
                    DIVIDE ( [Warehouse_OnHand_Ecom_EOP], [Units_Ecommerce], 0 ) * countDays
                ),
        "Skutecznosc_total",
            VAR __BASELINE_VALUE = [Units_SUM] + [DM_Total_EOP]
            VAR __VALUE_TO_COMPARE = [Units_SUM]
            RETURN
                IF (
                    NOT ISBLANK ( __VALUE_TO_COMPARE ),
                    DIVIDE ( __VALUE_TO_COMPARE, __BASELINE_VALUE )
                ),
        "Skutecznosc_sklepy_detal",
            VAR __BASELINE_VALUE = [Units_Detal] + [Store_OnLocalization_EOP]
            VAR __VALUE_TO_COMPARE = [Units_Detal]
            RETURN
                IF (
                    NOT ISBLANK ( __VALUE_TO_COMPARE ),
                    DIVIDE ( __VALUE_TO_COMPARE, __BASELINE_VALUE )
                ),
        "Skutecznosc_Ecom",
            VAR __BASELINE_VALUE = [Units_Ecommerce] + [Warehouse_OnHand_Ecom_EOP]
            VAR __VALUE_TO_COMPARE = [Units_Ecommerce]
            RETURN
                IF (
                    NOT ISBLANK ( __VALUE_TO_COMPARE ),
                    DIVIDE ( __VALUE_TO_COMPARE, __BASELINE_VALUE )
                ),
        "Total_Old_Stock",
            CALCULATE (
                [Warehouse_OnHand_EOP],
                'DWH DimWarehouse'[WarehouseVirtualName] = "Magazyn Centralny - Old Stock"
            ),
        "Warehouse_OnHand_Ecom_EOP", [Warehouse_OnHand_Ecom_EOP],
        "DM_Product_ReservationAlocation", [DM_Product_ReservationAlocation],
        "DM_Product_WarehousePacking", [DM_Product_WarehousePacking],
        "DM_Product_WarehouseNoSales", [DM_Product_WarehouseNoSales],
        "DM_Product_WarehouseNA", [DM_Product_WarehouseNA],
        "DM_Product_BlockForSuppliersMagazine", [DM_Product_BlockForSuppliersMagazine],
        "DM_Product_Warehouse_BlockOther", [DM_Product_Warehouse_BlockOther],
        "Warehouse_Orders_Ecom_EOP", [Warehouse_Orders_Ecom_EOP],
        "DM_Product_InTransitStore", [DM_Product_InTransitStore],
        "Warehouse_InTransit_Ecom_EOP", [Warehouse_InTransit_Ecom_EOP],
        "DM_Product_InTransit_Warehouse", [DM_Product_InTransit_Warehouse],
        "DM_Product_InTransit_Deposit", [DM_Product_InTransit_Deposit],
        "DM_Product_InTransit_Store_NoLLL", [DM_Product_InTransit_Store_NoLLL],
        "DM_Total_EOP", [DM_Total_EOP],
        "Sales_Effectivness_Blank", [Sales_Effectivness_Blank],
        "Sales_Effectiveness_Store_NoEcom", [Sales_Effectiveness_Store_NoEcom],
        "Sales_Effectivness_Blank_Ecom", [Sales_Effectivness_Blank_Ecom],
        "Sales_Effectiveness_Store_Ecom", [Sales_Effectiveness_Store_Ecom],
        "Rotation", [Rotation],
        "Rotation_Ecom", [Rotation_Ecom],
        "Rotation_Store", [Rotation_Store],
        "Margin_Pct_InitialPrice_PL_LC", [Margin_Pct_InitialPrice_PL_LC],
        "Zdjecie", CALCULATE ( MAX ( DimProductSK[MainImage] ) ) & "&rsct=image/jpeg",
        "Margin_Pct_PLN", [Margin_Pct_PLN],
        "InitialPrice_PL_LC", [InitialPrice_PL_LC],
        "CurrentPrice_PL_LC", [CurrentPrice_PL_LC],
        "Discount_PL_LC", [Discount_PL_LC],
        "Discount_%_PL_LC", [Discount_%_PL_LC],
        "CostPrice_AVG_EOP", [CostPrice_AVG_EOP],
        "Cost_Price_Gross_AVG_ALL", [Cost_Price_Gross_AVG_ALL],
        "OrderValue_PLN_SUM_toDate", [OrderValue_PLN_SUM_toDate],
        "OrderUnits_SUM_toDate", [OrderUnits_SUM_toDate],
        "ToDelivery_Value_PLN_SUM_toDate", [ToDelivery_Value_PLN_SUM_toDate],
        "ToDelivery_Units_toDate", [ToDelivery_Units_toDate],
        "FirstReceiveMKDate", FIRSTDATE ( 'DWH V_FactShipmentsDate'[FirstReceiveMKDate] ),
        "AllocationDate", FIRSTDATE ( 'DWH V_FactShipmentsDate'[AllocationDate] ),
        "ETA_MAX", [ETA_MAX],
        "DeliveryDateID_MAX", [DeliveryDateID_MAX],
        "PriceDate_MAX", [PriceDate_MAX],
        "FirstAllocatedDate", [FirstAllocatedDate],
        "DM_Product_EcommerceDostepne", [DM_Product_EcommerceDostepne],
        "ETA_Warehouse_MAX", [ETA_Warehouse_MAX]
    ),
    DATESBETWEEN ( 'DWH DimDate'[DateName], @FromDWHDimDateDate, @TODWHDimDateDate ),
    PATHCONTAINS ( @ChannelPL, 'DWH DimLocalization'[ChannelPL] ),
    PATHCONTAINS ( @CountryPL, 'DWH DimLocalization'[CountryPL] ),
    PATHCONTAINS ( @DistrictName, 'DWH DimLocalization'[DistrictName] ),
    PATHCONTAINS ( @StrefaName, 'DWH DimLocalization'[StrefaName] ),
    FILTER (
        VALUES ( DimProductSK[AssortmentName] ),
        @AssortmentName = " All"
            || PATHCONTAINS ( @AssortmentName, DimProductSK[AssortmentName] )
    ),
    FILTER (
        VALUES ( DimProductSK[Gender] ),
        @Gender = " All"
            || PATHCONTAINS ( @Gender, DimProductSK[Gender] )
    ),
    FILTER (
        VALUES ( DimProductSK[AssortmentGroupName] ),
        @AssortmentGroupName = " All"
            || PATHCONTAINS ( @AssortmentGroupName, DimProductSK[AssortmentGroupName] )
    ),
    FILTER (
        VALUES ( DimProductSK[UpperMaterial] ),
        @Material = " All"
            || PATHCONTAINS ( @Material, 'DimProductSK'[UpperMaterial] )
    ),
    FILTER (
        VALUES ( DimProductSK[DepartmentName] ),
        @DepartmentName = " All"
            || PATHCONTAINS ( @DepartmentName, 'DimProductSK'[DepartmentName] )
    ),
    FILTER (
        VALUES ( DimProductSK[ProductType] ),
        @ProductType = " All"
            || PATHCONTAINS ( @ProductType, 'DimProductSK'[ProductType] )
    ),
    FILTER (
        VALUES ( DimProductSK[ProductCategoryName] ),
        @ProductCategory = " All"
            || PATHCONTAINS ( @ProductCategory, 'DimProductSK'[ProductCategoryName] )
    ),
    FILTER (
        VALUES ( DimProductSK[Collection_Slicer] ),
        @Collection = " All"
            || PATHCONTAINS ( @Collection, 'DimProductSK'[Collection_Slicer] )
    ),
    FILTER (
        VALUES ( DimProductSK[Season] ),
        @Season = " All"
            || PATHCONTAINS ( @Season, 'DimProductSK'[Season] )
    ),
    FILTER (
        VALUES ( DimProductSK[Intake] ),
        @Intake = " All"
            || PATHCONTAINS ( @Intake, 'DimProductSK'[Intake] )
    ),
    FILTER (
        VALUES ( DimProductSK[Exposure] ),
        @Exposure = " All"
            || PATHCONTAINS ( @Exposure, 'DimProductSK'[Exposure] )
    ),
    FILTER (
        VALUES ( 'DWH V_DimProperty'[Value] ),
        @Campaign = " All"
            || PATHCONTAINS ( @Campaign, 'DWH V_DimProperty'[Value] )
    ),
    FILTER (
        VALUES ( DimProductSK[SourceCountry] ),
        @SourceCountry = " All"
            || PATHCONTAINS ( @SourceCountry, 'DimProductSK'[SourceCountry] )
    ),
    FILTER (
        VALUES ( DimProductSK[OwnBrand] ),
        @OwnBrand = " All"
            || PATHCONTAINS ( @OwnBrand, 'DimProductSK'[OwnBrand] )
    ),
    FILTER (
        VALUES ( DimProductSK[Brand] ),
        @Brand = " All"
            || PATHCONTAINS ( @Brand, 'DimProductSK'[Brand] )
    ),
    FILTER (
        VALUES ( DimProductSK[Vendor] ),
        @Vendor = " All"
            || PATHCONTAINS ( @Vendor, 'DimProductSK'[Vendor] )
    ),
    IF (
        @Ean <> "*",
        PATHCONTAINS ( @Ean, DimProductSK[EANCode] ),
        ( CONTAINSSTRING ( DimProductSK[EANCode], @Ean ) )
    )
)
ORDER BY [Revenue_Net_PLN_SUM] DESC

EnterpriseDNA · May 23, 2024, 3:59am

Hi @Harris,

To improve the performance of your DAX query in a paginated report, consider these optimizations:

Limit Columns: Only include necessary columns in SUMMARIZECOLUMNS.
Reduce Filters: Simplify or combine filters where possible.
Use Variables: Store intermediate results in variables to avoid recalculating them.
Pre-Aggregate Data: If possible, pre-aggregate data in the source system.

Here’s an example of a more optimized approach:

DAXCopy codeEVALUATE
VAR FilteredData =
CALCULATETABLE (
DimProductSK,
ALL ( ‘DWH DimLocalization’[ChannelPL], ‘DWH DimLocalization’[CountryPL], ‘DWH DimLocalization’[NewStrefaName], ‘DWH DimLocalization’[Board], ‘DWH V_FactPurchaseOrders’[OrderStatus], ‘DWH DimLocalization’[Floors], DimProductSK[AssortmentID], DimProductSK[SystemType], ‘DWH DimWarehouse’[WarehouseVirtualName] ),
DimProductSK[SystemType] = “LLL”,
‘DWH DimLocalization’[Board] = “LLL”,
‘DWH V_FactPurchaseOrders’[OrderStatus] <> “0”,
NOT ( ‘DWH DimLocalization’[ChannelPL] IN { “Rozchody”, " Hurt B2B", “Hurt obcy”, “Marketplace” } ),
NOT ( ‘DWH DimLocalization’[CountryPL] IN { “Turcja”, “Bangladesz”, “Mongolia”, “Białoruś”, “Indie”, “Meksyk” } ),
NOT ( ‘DWH DimLocalization’[NewStrefaName] IN { “Austria”, “Kosowo”, “Niemcy”, “Outlet RO”, “Rosja”, “Turcja”, “Ukraina” } ),
NOT ( ‘DWH DimLocalization’[Floors] IN { “Sklep wzorcowy” } ),
DimProductSK[AssortmentID] NOT IN { -2, -1, 4000, 9000 },
‘DWH DimWarehouse’[WarehouseVirtualName] IN { “Magazyn LLL EU - Sklad Celny”, “Magazyn LLL S.A - Dystrybucyjny”, “Magazyn LLL S.A. - Przyjęć”, “Magazyn Centralny - Magazyn przyjec”, “Magazyn Centralny - Oclone”, “Magazyn Centralny - Old Stock”, “Magazyn Centralny - Przepakownia”, “Magazyn Centralny - W trakcie prac wewnetrznych”, “Magazyn Centralny - Wzorcownia”, “Magazyn Centralny - Zwózka Ecommerce”, “Magazyn Ecommerce” }
)
RETURN
SUMMARIZECOLUMNS (
DimProductSK[ModelColorName],
DimProductSK[EANCode],
DimProductSK[Collection_Slicer],
DimProductSK[Season],
DimProductSK[Intake],
DimProductSK[Brand],
DimProductSK[DepartmentName],
DimProductSK[ProductType],
DimProductSK[isOnLine],
“Units_SUM”, [Units_SUM],
“Revenue_Net_PLN_SUM”, [Revenue_Net_PLN_SUM]
– add more measures as needed
)

For more advanced optimization tips, consider checking the Data Mentor platform.

Cheers,

Enterprise DNA Support Team