# Dax based column taking more time to caluclate

Hi there,

I am calculating a column using DAx language. The new column is the rolling sum of 48 half-hourly values. The calculation of column is :

calculated col = sumx(filter(Table1, Table1[EpochHH] > = Ealier(Table1[EpcohHH] )-47 && Table1[EpochHH] <= Ealier(Table1[EpcohHH] ) ), Table[Value] )

In the above calculation, EpochHH is the unique number to identify a half-hour time slot within a day. I have got around 150,000 rows in Table1. When I ran the above command, it takes about 25 mins to calculate the new column. Is there any way to make the calculation quicker?

Sample file can be found here sample_file.pbix (1.6 MB)

Hi @leo_89,

If you could share the sample file it will be easy to check and guide you.

Thanks
Mukesh

Sometime power bi isn’t the tool to do this. Maybe your IT area would have a better solution to have some of the calculation done in the background before the file is created with different version numbers.

thanks Keith

Hi Leo_89

I have been playing around with your question and made a upload of 150.000 random values rows into Power BI Desktop.
Tried some formula`s like cumulative total, based upon the index from the large table, and tried to put it in a table visual, also very slow and received a message “could not display, not enough memory”, so even a worse experience.
Calculations and filtering from the fact table is apparently NOT fast, but lookup tables are fast normally.

If I understand correctly what you want (continue rolling totals from the last 48 last EpochHH, for 150.000 rows, you could do that easily in Excel, sorting the data upfront and make a column "Rolling EpochHH, summing the current and 47 rows above for each row.

That can easily imported in PowerBI

Not sure if you need the Rolling info for every row in the table, would a rolling per day be sufficient ?

Maybe someone knows a fast solution for those calculations, based on large tables in DAX.
Kind regards

@deltaselect, Yes, the value of 150,000 rows updates once a day so, doing calculations in excel does not seem to be an efficient solution.

I need rolling info for every row in the table. I have also attached a sample file with my post.

Hi @MK3010,

I have attached sample file with my post.

@Keith, I totally agreed with you. However, IT seems to be rigid to do any calculation outside the power BI.

ok…just a thought that’s all

Hi @leo_89, did the response provided by the users and experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

Hi @leo_89, we’ve noticed that no response has been received from you since the 23rd of April. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

@EnterpriseDNA, no it did not help at all.

Hi Leo,

Only just another thought about your request, could your rolling sum of 48 half-hourly times not be defined as a a rolling total per day ? Maybe that already meets your information requirement?
If that would be the case, you can easily (and fast) calculate this amount with
Total value per day = SUMX( VALUES(EpochHHFaktTable[Date]),sum(EpochHHFaktTable[Value]))

Hi @leo_89, did the response provided by @deltaselect help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

Hi @leo_89, we’ve noticed that no response has been received from you since the 27th of April. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

This video shows how to create a running total in power query - you might gain performance from column compression vs a dax column