Forum Discussion

LuukP's avatar
LuukP
Helper I
3 years ago
Solved

Resources Exceeded - help with formula

Hi everyone,

 

I have a table visual with a few columns based on a data table with about 750,000 rows.  When published, I get a Resources Exceeded warning meaning that it is using more than 1.3GB of memory.  So, I'm looking at the measures used in the table, and am hoping someone can help with a better formula for the same results.

 

Essentially, I am calculating WIP (work in progress) using formulas like these:

 

WIP30 =
var MinDateInContext = MAX('Date Table'[Date])-59
var MaxDateInContext = MAX('Date Table'[Date])-30
var W30 = CALCULATE(SUM('WIP Aging'[WFeeExp]),DATESBETWEEN(WIP[Wdate],MinDateInContext,MaxDateInContext),FILTER('WIP Aging', 'WIP Aging'[Winvnum] = 0 || 'WIP Aging'[Winvnum]=[LastInvNum]|| 'WIP Aging'[Winvdate]=BLANK()))+0
RETURN W30
 
I have a column for WIPCurrent, WIP30, WIP60, WIP90, WIP120 and WIPTotal that follow the same structure.  The table visual takes about 25 seconds to load.
 
So, a few questions:
1) Is there a more memory-effective way to do the calculation?
2) Is there a tool that can show the memory used by any specific measure?
 
Thanks in advance!
Need Help
  • LuukP's avatar
    LuukP
    3 years ago

    Thanks amitchandak .  Unfortunately, I have to do this report in 30 day increments...

     

    However, I was able to solve my own issue by using SUMMARIZE on my main table (which reduced the records by about 200,000) and changing my measures to point to the summarized table instead:

     

    1 - SUMMARIZE

    WIP Aging Summary = SUMMARIZE('WIP Aging', [WempID], [Winvnum], [Winvdate], [WCltID], [Wdate], "WIP", sum('WIP Aging'[WFeeExp]))

     

    2 - MEASURE

    WIP30 =
    var MinDateInContext = MAX('Date Table'[Date])-59
    var MaxDateInContext = MAX('Date Table'[Date])-30
    var W30 = CALCULATE(SUM('WIP Aging Summary'[WFeeExp]),DATESBETWEEN('WIP Aging Summary'[Wdate],MinDateInContext,MaxDateInContext),FILTER('WIP Aging Summary', 'WIP Aging Summary'[Winvnum] = 0 || 'WIP Aging Summary'[Winvnum]=[LastInvNum]|| 'WIP Aging Summary'[Winvdate]=BLANK()))+0
    RETURN W30
     
    Thanks for your response!
     
    Luuk

2 Replies

  • LuukP's avatar
    LuukP
    Helper I
    3 years ago

    Thanks amitchandak .  Unfortunately, I have to do this report in 30 day increments...

     

    However, I was able to solve my own issue by using SUMMARIZE on my main table (which reduced the records by about 200,000) and changing my measures to point to the summarized table instead:

     

    1 - SUMMARIZE

    WIP Aging Summary = SUMMARIZE('WIP Aging', [WempID], [Winvnum], [Winvdate], [WCltID], [Wdate], "WIP", sum('WIP Aging'[WFeeExp]))

     

    2 - MEASURE

    WIP30 =
    var MinDateInContext = MAX('Date Table'[Date])-59
    var MaxDateInContext = MAX('Date Table'[Date])-30
    var W30 = CALCULATE(SUM('WIP Aging Summary'[WFeeExp]),DATESBETWEEN('WIP Aging Summary'[Wdate],MinDateInContext,MaxDateInContext),FILTER('WIP Aging Summary', 'WIP Aging Summary'[Winvnum] = 0 || 'WIP Aging Summary'[Winvnum]=[LastInvNum]|| 'WIP Aging Summary'[Winvdate]=BLANK()))+0
    RETURN W30
     
    Thanks for your response!
     
    Luuk