Query Run Time Question

[chicagolarsons]

Hi,

Yesterday I used this tutorial by Rick de Groot to create a running total by group on a transaction variance table.  The table has 170,488 rows. I know and expect the query to take time to fnish running, because that buffered list is still time consuming to calculate. But my question is, how MUCH time should I give it before I kill it?  It's been running about half an hour, and it shows the status of my function, fxRunningTotal as "Evaluating." Just curious if anyone has insight and can give me some expectations.....is it working, or in some sort of endless loop?

[Anonymous]

Hi @chicagolarsons ,

If you are still facing the problem of running queries for a long time, I suggest you can use Tools > Start Diagnostics to check exactly which step or steps of the query are causing the slowness.

This function counts exactly how long each query step has been running.

Best Regards,
Dino Tao

[j_ocean]

It might be better to do your running total in DAX, that way it dynamically adjusts to filters. In PQ, whatever you hard coded is what the report builder/viewer is stuck with.

[Omid_Motamedise]

Half an houre for such amount of rows is too much, it should be done in less than a minute, 

[ZhangKun]

I looked at the article you mentioned and downloaded the sample file at the end of the article. I believe I have tested it enough (4 Excel files, a total of 200,000 rows, random numbers from 1 to 10,000 are classified and summed), and it takes less than a minute.

But one thing I am not sure about is that this method will read the file 3 times (I am not sure if it is caused by merging Excel files). If your data source comes from the Internet, it may also be due to network problems.