Forum Discussion

Mo_'s avatar
Mo_
Advocate I
2 years ago

Visual has exceed the available resources

Hi all,

 

as the title says, I have a visual that exceed the available resources. This happens when I try to put the following measure in a matrix visual and put e.g. "Item" in the columns.

 

 

SalesRate7Days = 
Var _FirstSalesDate = 
CALCULATE(
    MIN( FACT_revenueposition[Date] ),
    REMOVEFILTERS( DIM_date ),
    FACT_revenueposition[Quantity] > 0
)
Var _MaxSalesDate =
CALCULATE(
    MAX( FACT_revenueposition[Date] ),
    REMOVEFILTERS( DIM_date )
)
Var _LastSalesDate =
SWITCH(
    TRUE(),
    _FirstSalesDate +6 > _MaxSalesDate, _MaxSalesDate,
    _FirstSalesDate +6
)
Var _SalesRate =
DIVIDE(
    CALCULATE(
        [Sales],
        DATESBETWEEN( DIM_date[Date], _FirstSalesDate, _LastSalesDate )
    ),
    CALCULATE(
        [Sales],
        DATESBETWEEN( DIM_date[Date], _FirstSalesDate, _LastSalesDate )
    ) + [StockPiecesAmountPhysAvail7Days]
)
Var _SalesRateVL =
CALCULATE(
    DIVIDE(
    CALCULATE(
        [Sales],
        DATESBETWEEN( DIM_date[Date], _FirstSalesDate, _LastSalesDate )
    ),
    CALCULATE(
        [Sales],
        DATESBETWEEN( DIM_date[Date], _FirstSalesDate, _LastSalesDate )
    ) + [StockPiecesAmountPhysAvail7Days]
    ),
    REMOVEFILTERS( DIM_salesChannel ),   
    VALUES( DIM_distributionChannel[DistributionChannelLabel] ) 
)

Var _Result =
SWITCH(
    TRUE(),
    SELECTEDVALUE( DIM_distributionChannel[DistributionChannelLabel] ) IN { "MP", "B2B", "USA", "Block" }, BLANK(),
    ISBLANK( [StockPiecesAmountPhysAvail7Days] ), _SalesRateVL,
    _SalesRate
)

RETURN
_Result

 

 

 

The tables that the measure needs to iterate over are large (32 Mio. rows, 8 Mio. rows) which certainly is a contributing factor. However, I have a table with 130 Mio. rows and some measures that operate on that table which work fine, albeit slowly.

 

So I suspect that there is some optimization that could be done in the above measure. Do you see any redundant steps, anything I could improve upon? 

 

Thanks

Need Help

1 Reply

  • anmolmalviya05's avatar
    anmolmalviya05
    Super User
    2 years ago

    Hi Mo_, Hope you are doing good!

    Below are few points on which you can focus

    • Simplify REMOVEFILTERS: Use ALL or ALLSELECTED instead of REMOVEFILTERS to target specific filters, potentially reducing calculation complexity.

    • Combine CALCULATE Steps: Merge similar CALCULATE operations where possible to streamline the measure.

    • Optimize DATESBETWEEN: Since DATESBETWEEN can be resource-intensive, try narrowing the date range or using DATEADD for relative periods to improve performance.

    • Review SWITCH Logic: Ensure the SWITCH conditions are mutually exclusive and simple conditions are checked first. If SELECTEDVALUE is slow, consider alternatives like calculated columns.

    • Break Down the Measure: Divide the measure into smaller intermediary steps to identify and address specific performance bottlenecks.

    • Leverage Aggregations: Use aggregated tables or pre-aggregated data to reduce the runtime processing load.

    • Use of Variables: While variables improve readability, they might hinder optimization. Use them cautiously with complex dependencies.

     

    Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!