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?
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.
I don’t know but i would think about this.
thanks Keith
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
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.
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.
