Resources Exceeded - help with formula

[LuukP]

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!

[LuukP · Accepted solution]

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

[amitchandak]

@LuukP , if you have a date in visual you can consider the Window function

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc