cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
DouweMeer Member
Member

Add customn column with dynamic table and memory problem

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?

1 ACCEPTED SOLUTION

Accepted Solutions
DouweMeer Member
Member

Re: Add customn column with dynamic table and memory problem

@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. 

2 REPLIES 2
Community Support Team
Community Support Team

Re: Add customn column with dynamic table and memory problem

@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 Member
Member

Re: Add customn column with dynamic table and memory problem

@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. 

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 83 members 1,665 guests
Please welcome our newest community members: