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:
max ( 'case history - new'[createddate] ) ,
'case history - new' ,
'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]
maxx ( table1 , [createddate2] ) ,
filter ( table1 ,
[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?