Not enough memory to complete this operation

[Umidvar]

I wanted to created a new column to get numbers per product depending on date column in Power BI Desktop but it gives error not enough memory to complete this operation. I have also replaced EARLIER() with variables but it shows the same error. There are only 350 000 rows. How can I fix it?

Column = CALCULATE( COUNTROWS(Task),

FILTER(Task,Task[Date]<=EARLIER(Task[Date])),

FILTER(Task,Task[Task Name]=EARLIER(Task[Task Name])),

FILTER(Task,Task[Index]<=EARLIER(Task[Index])))

Variable version =

var Tarix = Task[Date]

var Name = Task[Task Name]

var Indeks = Task[Index]

RETURN

CALCULATE(COUNTROWS(Task),

ALL(Task),

Task[Date]<=Tarix && Task[Task Name]=Name && Task[Index]<=Indeks)

[jdbuchanan71]

If you don't add 1 then the first day of the production will return 0 since the date on the row and the first date will be the same.

[Umidvar]

Thank you vey much! Apperciate you for your assistance.

[jdbuchanan71 · Accepted solution]

For that we just need to find the lowest date for the product and comare that to the date of the row.

This would be adding a calcualted column to your table.

Production Day = 
VAR _ProdStart = CALCULATE ( MIN ( 'YourTable'[Date] ), ALLEXCEPT ( 'YourTable', 'YourTable'[Product] ) )
RETURN 'YourTable'[Date] - _ProdStart + 1

I have attached my sample file for you to look at.

[Umidvar]

Hi Jdbuchanan,

Thank you very much for your assistance and guidance. That was well worked, but 1 more thing I would like to clarify with you: why did you add 1 to calculation at last step?

[jdbuchanan71]

Please create dummy data that replicates your format and also show your expected results and the logic you applied. 

[Umidvar]

Thank you very much for your reply. I need to add a cloumn which shows the result like in Production day column starting from first sales date as I need to get sales revenue for first 14 days of each product. After getting column or measure result, need to add it as slicer to report so that it could be easier to get the result for all product by slicer for their first 14 days sales revenue.

[jdbuchanan71]

Please share some sample data and your expected results.  Take a look at this post about how to provide sample data.

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

[Umidvar]

Due to the privacy, I am unable to share the details of file. I can bring an example as :

There are 6 products in product column and there are some subproducts of these products in another column (for example product is pen and different pens with different colors, but need to make calculation on products). There are duplicated dates and products as different subproducts were produced and sold, but the name of product is same at product column (task name column) as this subproduct's main box. The starting production date is different for all of these products. I should add a slicer as 1-14 which shows first True 14 days of each product from its production date (ignoring subproduct as need to make calculation for product column).

[jdbuchanan71]

When you use FILTER over a whole table, the engine has to load the entire table into memory.  Your measure was doing this 3 times so that is why you were running out of memory.   You should be able to add your column like this.

Column =
CALCULATE (
    COUNTROWS ( Task ),
    ALL ( Task ),
    Task[Date] <= EARLIER ( Task[Date] ),
    Task[Task Name] = EARLIER ( Task[Task Name] ),
    Task[Index] <= EARLIER ( Task[Index] )
)

[Umidvar]

Thanks for your reply. But it doesn't give me the separate calculation for each product from 1 (first day of product from each product separately) as I need to show the first True 14 days of each product