Visualization Error: "This visual has exceeded the available resources."

[patri0t82]

Hello, I'm wondering if someone can help me tune this measure below. It is causing my chart to return the error message in the title.

It's returning the exact result I need, but on my desktop app the charts are very slow to load, and in the online service they will simply not load. 

Help is greatly appreciated.

5YR Trailing MTBF =

DIVIDE(CALCULATE(DISTINCTCOUNT('MTBF - Combined'[Equipment ID]),ALLSELECTED('MTBF - Combined')),

(

CALCULATE ([Failures - Rolling 13], PARALLELPERIOD(CalendarTable[Calendar].[Date], 0, MONTH )) +

CALCULATE ([Failures - Rolling 13], PARALLELPERIOD(CalendarTable[Calendar].[Date], -12, MONTH )) +

CALCULATE ([Failures - Rolling 13], PARALLELPERIOD(CalendarTable[Calendar].[Date], -24, MONTH )) +

CALCULATE ([Failures - Rolling 13], PARALLELPERIOD(CalendarTable[Calendar].[Date], -36, MONTH )) +

CALCULATE ([Failures - Rolling 13], PARALLELPERIOD(CalendarTable[Calendar].[Date], -48, MONTH ))

)

) * 60 + 0

For reference,

Failures - Rolling 13 = CALCULATE (

'MTBF - Combined'[Failures - Monthly],

DATESBETWEEN (

CalendarTable[Calendar],

NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( CalendarTable[Calendar] ) ) ),

LASTDATE ( CalendarTable[Calendar] )

))

and subsequently

Failures - Monthly =

CALCULATE(

SUM('MTBF - Combined'[Failure]),

DATESINPERIOD('MTBF - Combined'[Order Creation Date], MAX('MTBF - Combined'[Order Creation Date]), -12, MONTH)

) + 0

[patri0t82 · Accepted solution]

I believe I've made some progress, it's slightly faster, though still not ideal.

5YR Trailing MTBF =

DIVIDE(CALCULATE(DISTINCTCOUNT('MTBF - Combined'[Equipment ID]),ALLSELECTED('MTBF - Combined')),

[Prev60Months]

) * 60 + 0

Prev60Months =

CALCULATE(

SUM('MTBF - Combined'[Failure]) ,

DATESINPERIOD(CalendarTable[Calendar],

MAX(CalendarTable[Calendar]) +0 , -5, YEAR)

)

I would still be grateful for any assistance.

[v-kelly-msft]

Hi @patri0t82 ,

Try:

Prev60Months =
CALCULATE(
SUM('MTBF - Combined'[Failure]) ,FILTER('MTBF-Combined',YEAR('MTBF-Combined'[Date])>=YEAR(MAX('CalendarTable'[Date]))-5 && YEAR('MTBF-Combined'[Date])<=YEAR(MAX('CalendarTable'[Date]))))

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[patri0t82]

Thank you for the reply, I appreciate you taking the time to work out the measure. It, however, wasn't performing exactly as desired.

I added it as a column in my table visual on the far right as you'll see in the image below:

The result showing is 22, which is the total number of failures only for that month. It should be showing the total for the previous 5 years. (which should be 4485 I believe (calculated at the month level))

Anyway, unless there's something simple standing out, I really appreciate your response and think the solution I have in place is working fine.

[v-kelly-msft]

Hi  @patri0t82 ,

If possible,could you pls share your .pbix file for test?

Remember to remove confidential information.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[patri0t82]

I really appreciate your help, and I think the solution I have from above will continue to work well. It would be too much work anyway to replace with dummy data. Thank you for your support.