Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  How to Optimize my dax query

    Posted Aug 12, 2022 06:56 AM

    Hi Guys,

    I realy apriciate 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 Month Forecast Absolute 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 (
    MERCH_FORECAST_SNAPSHOT[_Raw Forecast Qty],
    MERCH_FORECAST_SNAPSHOT[Caldate Month Index] >= LE
    && MERCH_FORECAST_SNAPSHOT[Caldate Month Index] <= MaxLE)

     

    VAR _AbsoluteDiff = SUMX(VALUES(ARTICLE[Article]),Calculate(ABS(ForecastQty-ActualQty)
    ))
    VAR
    _Accuracy = IF(ActualQty,ABS(
    1- DIVIDE(_AbsoluteDiff,ActualQty,0)))

    Return
    _Accuracy

     

     

    Thank you in Advance. 



    ------------------------------
    Sree Sree
    Software developer
    Bangalore SD
    ------------------------------


  • 2.  RE: How to Optimize my dax query

    Posted Aug 18, 2022 07:53 PM
    @Sree Sree,
    It's really impossible to answer your question without a .pbix file to review.  We need to be able to see the data model, and understand the relationship between the tables, and see how the other measures are calculated.

    If you post a .pbix file, I think you might get some people pitching in to help.

    Kaz.​

    ------------------------------
    Kaz Shakir
    Sr. Program Manager, Asset Planning
    TN
    ------------------------------