Forum Discussion

rehansaeed2468's avatar
rehansaeed2468
Icon for Helper I rankHelper I
10 months ago
Solved

Query Exceeded Available Resource

I am getting the "Query Exceeded Available Resources" if I am using the below Measure.   VAR Alpha = 0.964783                       VAR CurrentIndex = SELECTEDVALUE('Table B'[Row Index_TableB col...
Need Help
  • v-echaithra's avatar
    v-echaithra
    10 months ago

    Hi rehansaeed2468 ,

    Create the helper table first:

    IndexTable =
    DISTINCT (
    SELECTCOLUMNS (
    'Table B',
    "RowIndex", 'Table B'[Row Index_TableB]))


    Now create the DecayFactor column inside IndexTable (not Table B):

    DecayFactor =
    POWER ( 0.964783, IndexTable[RowIndex] )


    This will work because of no circular dependency.


    Final Measure =
    VAR CurrentIndex =
    SELECTEDVALUE ( 'Table B'[Row Index_TableB column] )

    RETURN
    IF (
    ISBLANK ( CurrentIndex ),
    BLANK (),
    SUMX (
    FILTER (
    IndexTable,
    IndexTable[RowIndex] <= CurrentIndex
    ),
    IndexTable[DecayFactor]
    * CALCULATE (
    SUM ( 'Table B'[Product Cost] ),
    'Table B'[Row Index_TableB] = IndexTable[RowIndex]
    )))

    Hope this helps.
    Chaithra E

Amar_Kumar's avatar
Amar_Kumar
Icon for Super User rankSuper User
10 months ago

rehansaeed2468 Try creating it as a measure

DecayFactor :=
VAR Alpha = 0.964783
VAR CurrentIndex =
SELECTEDVALUE ( 'Table B'[Row Index_TableB column] )
RETURN
POWER (
Alpha,
CurrentIndex - MIN ( 'Table B'[Row Index_TableB] )
)

rehansaeed2468's avatar
rehansaeed2468
Icon for Helper I rankHelper I
10 months ago

NOw getting the same messsage as earlier "Query has exceeded the vaailable resources"

  • Amar_Kumar's avatar
    Amar_Kumar
    Icon for Super User rankSuper User
    10 months ago

    rehansaeed2468 Try with a helper table,

    IndexTable =
    DISTINCT (
    SELECTCOLUMNS (
    'Table B',
    "RowIndex", 'Table B'[Row Index_TableB]
    )
    )

    Then Add decay factor as a column

    DecayFactor =
    POWER ( 0.964783, [RowIndex] )

     

    Final Measure:-

    Final Measure :=
    VAR CurrentIndex =
    SELECTEDVALUE ( 'Table B'[Row Index_TableB column] )

    RETURN
    IF (
    ISBLANK ( CurrentIndex ),
    BLANK (),
    SUMX (
    FILTER ( IndexTable, IndexTable[RowIndex] <= CurrentIndex ),
    IndexTable[DecayFactor]
    * CALCULATE (
    SUM ( 'Table B'[Product Cost] ),
    'Table B'[Row Index_TableB] = IndexTable[RowIndex]
    )
    )
    )

     

    this should not give you the error. If this still gives error, the issue would be data corruption or model size, not DAX logic.

    • rehansaeed2468's avatar
      rehansaeed2468
      Icon for Helper I rankHelper I
      10 months ago

      I f I am trying to create the Decayfactor column its not findng the [Rowindex] even though I already have the IndexTable working.

       

      • v-echaithra's avatar
        v-echaithra
        Icon for Community Support rankCommunity Support
        10 months ago

        Hi rehansaeed2468 ,

        Create the helper table first:

        IndexTable =
        DISTINCT (
        SELECTCOLUMNS (
        'Table B',
        "RowIndex", 'Table B'[Row Index_TableB]))


        Now create the DecayFactor column inside IndexTable (not Table B):

        DecayFactor =
        POWER ( 0.964783, IndexTable[RowIndex] )


        This will work because of no circular dependency.


        Final Measure =
        VAR CurrentIndex =
        SELECTEDVALUE ( 'Table B'[Row Index_TableB column] )

        RETURN
        IF (
        ISBLANK ( CurrentIndex ),
        BLANK (),
        SUMX (
        FILTER (
        IndexTable,
        IndexTable[RowIndex] <= CurrentIndex
        ),
        IndexTable[DecayFactor]
        * CALCULATE (
        SUM ( 'Table B'[Product Cost] ),
        'Table B'[Row Index_TableB] = IndexTable[RowIndex]
        )))

        Hope this helps.
        Chaithra E