Running into Memory Issue

[ArsalanFND]

Hello People!

I will be brief and straight to the point.

i have a dataset as follows:

Date_Time                        Entry  Exit  (entry - Exit) 
12/21/2016 12:00:00 AM    5       4          1 
12/21/2016 12:15:00 AM    3       1          2

The DateTime field is bins of 15 mins (96 intervals) for each day for three years of data.

What i am trying to get is a Running Total of the Calculated column (Entry-Exit). Which i get by quick measure option.

The problem i am facing is Can't Display Visual: Not enough Memory. If i calcualte the runninig total by day then it would wrok. But as soon as i go for Hourly or 15 mins interval it give me the error.

I have also tried to do the same on 1 year of data but still same issue.

Is there any efficent way to do it so i dont Run into memory problems?

**Ultimate goal is to calculate the running total of (entry-exit) per 15 mins and then get the Max of that value for the day. And plot a line graph that shows Max Running total for each day for all three year.

Any idea / help greatly appreciated. Thanks

Cheers,

FND
 

[Anonymous]

HI @ArsalanFND,

Nope, it seems like your intervals split your records to too many sub-records which need to calculate and progress in memory.

I can only suggest you use 64 bit power bi desktop with large physical memory or prepare a detail table with detailed interval records to reduce calculate pressure.

In addition, you can also take a look at following link if your requirement is similar as below:

DAX - 15 minute Interval Question

Regards,

Xiaoxin Sheng

[ArsalanFND]

Hi,

Also i able to create the measure with Running total. How can i calculate MAX for the day? for visuals.

Running total is measure and DateTime is a column from another table with realtionship.

Thansk,

Cheers,

[ArsalanFND]

So I got the Max values. NOw my only issue is still i am unable to visualize MAX for the day. should i use SQL databse instead of Excel files to load and process m data faster??

Thanks,

[Anonymous]

HI @ArsalanFND,

I'd like to suggest you create a table to store expand records, then you only need to do summarize on real records. In my opinion, it will spend less system resource than calculate with huge virtual table.

Regards,

Xiaoxin Sheng

[ArsalanFND]

Hi,

Yes thats what i am trying to do now, I have extract the table to get just the number of rows i want to het the running total of. So the max rows now i am dealing with are 37,678.

I am using the following formula to creatre a new column to calculate the running total.

Occupancy = CALCULATE(
SUM('Table'[Occ]),
ALL('Table'),
'Table'[Index] <= EARLIER('Table'[Index])
)

But i am still facing not enough memory problem. 

** It does calculate the running total if i reduce my number of rows. (which i can't no more)

My machine has 16 gb of RAM, upgrading RAM would help me in this case? or there is something i am not doing efficently. 

Thanks,

[Anonymous]

Hi @ArsalanFND,

Hmm, I guess you are using a 32 bit version of power bi right? If this is a case, you can download and use 64 bit version to allow power bi to use more memory resource.

Regards,

Xiaoxin Shengm

[ArsalanFND]

Hi Xiaoxin,

Thanks for the reply, yes correct the sun-records are too many. 

The link you provide was helpful but i am ok with that part.

I am calculationg the Running Total based on my Date Time Table which as 3 years each day with 96 intervals. Which is a huge calculation every time. 

Is there a way i can calculate it and save it so it's not calculated every time?

Thanks,

FND