Calculated column throws not enough memory error! any Smart alternates to approach this ?

[Anonymous]

Is there anyway to fine tune this or use Measures to achieve this New_Return Column? 

I've got 7 Columns in this table and query works with filter till 2017 May 1st and beyond that throws error "There's Not Enough Memory To Complete This Operation"

Running on 16GB and upgrading is not an option, my boss doesnt want to spend money 😞

New_Return=
VAR rule1 =
CALCULATE (
COUNTROWS ( Table1 ),
FILTER (
ALLEXCEPT ( Table1, Table1[cust_num] ),
Table1[order date] < EARLIER ( Table1[order date] )
)
)
VAR Calc =
CALCULATE (
MIN ( Table1[order date] ),
FILTER (
ALLEXCEPT ( Table1, Table1[cust_num] ),
Table1[order date] < EARLIER ( Table1[order date] )
)
)
VAR rule2 =
IF ( NOT ( ISBLANK ( Calc ) ), DATEDIFF ( Calc, Table1[order date], DAY ) )
RETURN
IF ( OR ( rule1 = 0, rule2 > 100 ), "New", "return" )

)

My initial post is here 

http://community.powerbi.com/t5/Desktop/Return-Customer-and-New-Customer-with-no-purchase-in-last-10...

Any suggestions would be greatly appreciated

Thank you

Van

[nmck86]

Have you tried to remove the filters?

Column = VAR rule1=CALCULATE(COUNTROWS(Table1),ALLEXCEPT(Table1,Table1[cust_num]),Table1[order date]<earlier(Table1[order date])) VAR Calc=CALCULATE(min(Table1[order date]),ALLEXCEPT(Table1,Table1[cust_num]),Table1[order date]<earlier(Table1[order date])) VAR rule2=if(NOT(ISBLANK(Calc)),DATEDIFF(Calc,Table1[order date],DAY)) RETURN IF(OR(rule1=0,rule2>100),"New","return")

[Anonymous]

Thanks for our reply, Yes i did try removing filters, no luck. I cant believe this calculation is talking up 16 GB! 

There should be something that i can tune , ill update if I find any solution.

Thank you

Van

[Anonymous · Accepted solution]

So after few failures i found this is the best way to do it, created new table with only 3 columns and used the same DAX calculation and it refreshes in less than 30 sec.

Cheers

Van

[Anonymous]

Hi Van, 

I realize this was a while ago, but I am facing the same issue now. What columns did you use for your new table and did you join it to your original table?