Forum Discussion
Query Exceeded Available Resource
I am getting the "Query Exceeded Available Resources" if I am using the below Measure.
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
9 Replies
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.
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]
)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] )
)NOw getting the same messsage as earlier "Query has exceeded the vaailable resources"
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.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.
