Add customn column with dynamic table and memory problem

[DouweMeer]

So I'm tring to get around the memory problem. 

What my goal is to get how long a certain case has been on a the status "Pending". So what I thought I would do is create a table for when cases were removed from status "Pending" and an additional table for when they were put on status "Pending". These tables created by Power Query. 

Then I tried to create a custom column with the expression:

calculate ( 

   max ( 'case history - new'[createddate] ) ,

   filter (

      'case history - new' ,

      and (

         'case history - new'[case id] = 'case history - old'[case id] ,

         'case history - new'[createddate] <= 'case history - old'[createddate]

         )

      )

   )

My problem with this way is that I have 19k+ records of both tables. The amount of memory required crosses my 16 GB memory laptop. Within 4 years, I expect the amount of records to be 4 times larger and still relevant in its full size. So no cutting on records by Power Query. 

So I thought I give something else an attempt. I removed the second table and put everything in 1 once again. This time I thought I would try something I've never done before, create a dynamic table within a custom column :

Previous date = 

VAR table1 =

selectcolumns ( 'case history' ,

"createddate2" , 'case history'[createddate] ,

"caseid2" , 'case history'[caseid] ,

"new2" , 'case history'[newvalue]

)

Return

calculate (

maxx ( table1 , [createddate2] ) ,

filter ( table1 ,

   and (

      [createddate2] <= 'case history'[createddate] ,

      [caseid2] = 'case history'[caseid]

      )

))

I know selectcolumns works when I use it as a dynamic table, but somehow I'm unable to filter the available date to create table1, so it will only contain data based on the record date of 'case history'. I could do it, but then I have to do a similar trick as before which will run me dry of memory and I'll be stuck once again. 

Thoughts that passed was pivot the table so I have on 1 records both the old and new creation dates, but as there might be multiple times a case has been put on pending, I would suggest it won't work either. Other solutions involved creating more tables and thus eating more memory. 

Any suggestions to get what I want without running my memory dry?

[v-yuta-msft]

@DouweMeer,

This is an issue about DAX performance, I would suggest you to refer to this Topic and docs below:

https://docs.microsoft.com/en-us/power-bi/power-bi-reports-performance

http://blog.pragmaticworks.com/power-bi-performance-tips-and-techniques

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DouweMeer · Accepted solution]

@v-yuta-msft 

Actually, I've found my answer. I've combined both selectcolumns and the solutions for when distinct shows blank values. 

My assumption would be that the referential integrity on an update kills your memory.