Forum Discussion

Srikantht's avatar
Srikantht
Helper II
4 years ago

How to Optimize my Complex dax query

Hi Guys, I really appriciate if some one gives solution for the below dax query..    The below dax query need to be optimized. How?  this query running 2400 ms. So I want to optimize it.    1 Mo...
Need Help
Srikantht's avatar
Srikantht
Helper II
4 years ago

Hi Guys,

 

Here is the Peformance of this Query in dax studio. and using matrix visual & there are 4 rows (LE fiscal period, Major code, Minor code & family code)

 

 

Help me where the  performace is slow ? 

 

Thanks.

bcdobbs's avatar
bcdobbs
Community Champion
4 years ago

A few questions to start with...

 

You have:

 

VAR MaxLE = LE

 

Then go onto use them as if they were different. Is that correct?

 

Later on you reference two variables inside a calculate on a sumx:

Calculate(ABS(ForecastQty-ActualQty)

Once assigned to a variable quanties don't get revaluated inside a filter context. Are you sure you're getting the correct answers? If you are you would get same value by removing the sumx and simply multiplying the absolute difference by the number of articles which is quicker to find with a COUNTROWS.

 

 

  • Srikantht's avatar
    Srikantht
    Helper II
    4 years ago

    HI ,

     

    Thank you for responding.

     

    MaxLE = LE  - using to add like (LE+1, LE+2 ) for 2 months, 3 months forecast accuracy. Now this is for 1  month. 

     

    sorry for the above query.

     

    Here is the chaged query & the above performace screen shot is the below query.

     

    _Raw 1 month Forecast Accuracy =
    VAR LE =
    MAX ( MERCH_FORECAST_SNAPSHOT[LE Month Index] )
    VAR MaxLE = LE
    VAR MaxActul = CALCULATE(MAX ( MERCH_FORECAST_SNAPSHOT[Caldate Month Index] ),MERCH_FORECAST_SNAPSHOT[Actuals Flag]="1"
    ,ALL(MERCH_FORECAST_SNAPSHOT[Forecast Scenario]))

    VAR ActualQty =IF(MaxActul<MaxLE,0,
    CALCULATE (
    MERCH_FORECAST_SNAPSHOT[_Raw Actual Qty],
    ALL ( MERCH_FORECAST_SNAPSHOT[LE Fiscal Period] ),
    MERCH_FORECAST_SNAPSHOT[Caldate Month Index]>= LE
    && MERCH_FORECAST_SNAPSHOT[Caldate Month Index] <= MaxLE
    ))
    VAR ForecastQty =
    CALCULATE (
    [_Raw Forecast Qty],
    MERCH_FORECAST_SNAPSHOT[Caldate Month Index] >= LE
    && MERCH_FORECAST_SNAPSHOT[Caldate Month Index] <= MaxLE
    )
    Return
    IF(ActualQty,
    1- DIVIDE ( ABS ( ForecastQty - ActualQty ), ActualQty,0) )

     

    for this you can help me please. 

    Thanks.....