Query Exceeded Available Resource

[rehansaeed2468]

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 column]) //Caluclated Column

VAR MinIndex =

    CALCULATE(

        MINX( ALLSELECTED('Table B'), 'Table B'[Row Index_TableB] )  //Measure

    )

RETURN

IF(

    ISBLANK(CurrentIndex) || ISBLANK(MinIndex),

    BLANK(),

    CALCULATE(

        SUMX(

            FILTER(

                ALLSELECTED('Table B'),

                'Table B'[Row Index_TableB] <= CurrentIndex

            ),

            'Table B'[Product Cost] * POWER( Alpha, CurrentIndex - 'Table B'[Row Index_TableB] )

        )

    )

)

[v-echaithra]

Hi @rehansaeed2468 ,

May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.

Thank you.

[v-echaithra]

Hi @rehansaeed2468 ,

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We are always here to support you.

Thank you.

[rehansaeed2468]

Creating the below column is is giving me "A circular Dependency was detected" Error

DecayFactor =
POWER (
0.964783,
MAX ( 'Table B'[Row Index_TableB column] )
- 'Table B'[Row Index_TableB]
)

[Amar_Kumar]

@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]

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

[Amar_Kumar]

@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]

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]

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]

@rehansaeed2468 Try Pre filter ince using Calculatetable

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

VAR FilteredTable =
CALCULATETABLE (
'Table B',
FILTER (
ALLSELECTED ( 'Table B' ),
'Table B'[Row Index_TableB] <= CurrentIndex
)
)

RETURN
IF (
ISBLANK ( CurrentIndex ),
BLANK (),
SUMX (
FilteredTable,
'Table B'[Product Cost]
* POWER ( Alpha, CurrentIndex - 'Table B'[Row Index_TableB] )
)
)

--------------

If possible create a calculated column

DecayFactor =
POWER (
0.964783,
MAX ( 'Table B'[Row Index_TableB column] )
- 'Table B'[Row Index_TableB]
)

then your measure becomes

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

RETURN
CALCULATE (
SUMX (
'Table B',
'Table B'[Product Cost] * 'Table B'[DecayFactor]
),
FILTER (
ALLSELECTED ( 'Table B' ),
'Table B'[Row Index_TableB] <= CurrentIndex
)
)

this should be much faster.

please mark this as solution and close the thread.