Forum Discussion

patri0t82's avatar
patri0t82
Icon for Post Patron rankPost Patron
5 years ago
Solved

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

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
Need Help
  • patri0t82's avatar
    patri0t82
    5 years ago

    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.

5 Replies

  • patri0t82's avatar
    patri0t82
    Icon for Post Patron rankPost Patron
    5 years ago

    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's avatar
      v-kelly-msft
      Icon for Community Support rankCommunity Support
      5 years ago

      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's avatar
        patri0t82
        Icon for Post Patron rankPost Patron
        5 years ago

        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.